Post reply

User Mapping in Security Logins

  • May 27, 2008 at 6:56 am

    #186145

    i came across a strange problem today. i restored UAT database from latest production backup file using SSMS and also selected to overwrite the existing one. After successful restoration i realized that user mapping for this particular backed up database for all the security logins got cleared.

    i have to reset all logins with database roles for this database. is there anyway, when i restore the database from backed file, user mapping for this database doesn't get cleared?

    Thanks in advance.

  • May 27, 2008 at 8:17 am

    #820575

    The Users in a database and the Logins at a server level are mapped together by SID, not by user name. So, if you restore a user database onto a server with a Master database that did not come from the same server, the SID's will be different.

    There are lots of scripts for fixing them after doing your restore. I found one for you:

    http://www.databasejournal.com/scripts/article.php/1547401

  • May 27, 2008 at 6:36 pm

    #820904

    Thanks Michael for the clarification. i was scared yesterday of doing something wrong but it was lack of knowledge regarding restoration of databases.

    Cheers Mate 🙂

  • May 28, 2008 at 10:12 am

    #821293

    I get to deal with this quite a bit in our databases. If you are using SQL logins, these may be helpful to you. If you are associating database users with Windows logins, this will not work.

    This lists 'orphaned' logins.

    sp_change_users_login 'Report'

    This stored procedure fixes ophaned users in the database by attaching them to a matching SQL login name.

    sp_change_users_login 'Auto_Fix', '[insert username here]'

  • May 28, 2008 at 10:16 am

    #821298

    Unless you are crossing domains when crossing servers, Windows SIDs should map just fine, provided the logins were created previously on the new servers. A good thing to do is to script out all of the logins on a recurring basis and store the script in a place which gets backed up. Not only does this serve a disaster recovery purpose, but it also allows you to "recover" a login with the exact password (the script will store it in its encrypted form) so even if you're having to create it again, you don't have to know what the original password was.

    How to transfer the logins and the passwords between instances of SQL Server 2005 (918992)[/url]

    K. Brian Kelley
    @kbriankelley

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

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