SQL Server Performance

Logins transfer problems.

Discussion in 'SQL Server Log Shipping' started by arturszcz, Apr 21, 2007.

  1. arturszcz New Member

    Hi,

    I've setup Log Shipping between two SQL 2000 Servers Ent. (64 bit editions).
    Almost everything works fine with one exception.
    I see that Log Shipping doesn't transfers any logins to secondary server.
    Example:
    If I add new user on primary server he doesn't appears on secondary server.
    This is very strange, because it works fine on my testing servers (32 bit).
    Any ideas?
  2. MohammedU New Member

    Log shipping will not transfer the logins...

    Check the following...

    How to configure security for SQL Server log shipping
    http://support.microsoft.com/kb/321247

    Frequently asked questions - SQL Server 2000 - Log shipping
    http://support.microsoft.com/kb/314515

    How to transfer logins and passwords between instances of SQL Server
    http://support.microsoft.com/kb/246133


    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=20594


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. arturszcz New Member

    Hi,

    I understannd that I have to make DTS package to move users then bcp do create dat file with logins and resolve logins storep procedure. This is simple and i have no problem with this.

    My problem is that I can't see any users (in users view in EM) in Log Shipped database on secondary server.
    On my testing serwer when I add new user to primary server the same users appears on secondary server. Ofcourse SID is different.
    Why it doesn work on my production server?

    I've found this in documentation. Maybe this is my main problem?

    "Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task."

  4. MohammedU New Member

    Users are part of the database and I believe you should see them in log shipped database when you add new user.

    Did you check through QA?

    select * from <dbname>..sysusers where name = <New user name>


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. arturszcz New Member

    It looks like logins exist now in database. I've setup Log Shipping one more time.
    If I run your command or sp_change_users_login 'report' I can see logins.
    Now I'm trying to make sid change but it doesn't work.

    I have syslogins.dat file form my primary server (bcp copy).
    I use command:

    use master
    EXEC sp_resolve_logins
    @dest_db = 'HURT',
    @dest_path = 'z:',
    @filename = 'syslogins.dat'

    After that i'm trying to compare SID's between my primary and secondary servers I can see that SID's are different.
    One think... I didn't change my secondary server to primary (role change).
    I need secondary server for reporting and I want to use the same logins.
    Maybe that's why sp_resolve_logins doesn't work?

  6. satya Moderator

    Why don;t you run SP_CHANGE_USERS_LOGIN to see any mismatch of logins.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  7. arturszcz New Member

    I've run "sp_change_users_login" and I see all mismatched logins.
    I can always add login manualy with specified sid using "sp_addlogin" but I wonder why "sp_resolve_logins" procedure doesn't work?
  8. satya Moderator

    http://vyaskn.tripod.com/troubleshooting_orphan_users.htm a good one to explain the scenario and fix.
    KBAhttp://support.microsoft.com/kb/298758 fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page