Post reply

Copied User DB, Restored Master DB but still had orphaned users.

  • January 6, 2004 at 9:25 am

    #83284

    We transferred our User DBs from Server_A to Server_B. We noticed that we had some orphaned users in our User DBs on Server_B. So, we restored the latest Master DB backup from Server_A to Server_B. Still, we noticed some orphaned users in the User DBs on Server_B. Is this possible? If so, why? We are running SQL Server 2000, SP 3, on Windows 2000 Server.

     

  • January 6, 2004 at 10:23 am

    #488824

    This is because the DB users are stored in the sysusers table in each user DB whereas the login info is stored in the master DB so when you restored the user DB on another server you effectively dropped the login for those users that do not have a login on the target server.  This leaves those users orphaned in the database.

    When you restored master DB from Server A to Server B, the server SID in the syslogins table in the master DB would not match the target Server B, so some other orphaned users may resurface.

    This is why it is necessary to recreate all logins that exist in the originating server on the target server before you perform a DB restore.


    Joseph

  • January 6, 2004 at 10:29 am

    #488826

    sp_help_revlogin on the MS Technet site can script out old logins that you can transfer to the new server. DTS has a "TRansfer Users" task as well in SQL 2000.

    Also, sp_change_users_login will resynch the logins and users as explained in the post above.

  • January 6, 2004 at 10:30 am

    #488829

    sp_change_users_login

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply