Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Restores Mapping Users to Logins


By:   |   Last Updated: 2006-09-15   |   Comments (1)   |   Related Tips: More > Restore

Problem

When restoring a database to a different server there is often the problem of matching up logins and users.  The reason for this is that login information is stored in the master database and user information is stored in the specific database you are working with. The login information is stored in the sysxlogins table in the master database. This table contains a column that holds the SID (Security Identifier) which is tied to a specific login. A corresponding table sysusers is stored in each user database and uses the SID to determine if a login has database access. So even though you may already have the same logins on your new server the SID may not match. What makes this even more confusing is that you can see the actual names of the logins and users, so you would think they would automatically match up, but the SID is what is actually tying the security together not the name.

One simple solution would be to restore the database drop all of the users and recreate the users.  This may be simple if you have a few users, but what if there are hundreds.  Also, if the user owns objects you will not be able to drop the user.

Solution

SQL Server offers several different commands to allow you to relink the logins and the users. Following is a list of the different commands and how they can be used.  Remember there are two different types of SQL Server logins standard and Windows authenticated, so there are different approaches for each.

Users

After you restore the database you can see what users exist in the database.  These users then need to be matched up with the corresponding logins on the server.

Standard Logins

If a standard login already exists you can use this command sp_change_users_login to relink the login and the user.

sp_change_users_login @Action='Report'

This stored procedure has three options Auto_Fix, Report and Update_One, below is a brief description of the options.

  • The Auto_Fix option will link users and logins that have the same name.
  • The Report option will show you a list of users in the current database that are not linked to a login.
  • The Update_One option allows you to link a user and login that may not have the same exact name.

Removing Users

If there are users in the database and there is not a corresponding login on the server and you do not need this user anymore, you can use this command sp_revokedbaccess to remove the user from the database and cleanup the user list.

sp_revokedbaccess 'NorthDomain\Mike'

Windows Logins

If your NT login and user names do not match you can use the following commands to link your NT login and user information. Microsoft does not recommend updating system tables directly, so you should only use this if you totally understand what you are doing and how to recover if there is a problem. You will also need to change the server setting to allow updates to system tables. In this query we are specifying the login "NorthDomain\Joe" and the database user "Joe", so you will need to update accordingly.

DECLARE @sysxlogins_sid VARBINARY(85)
SELECT @sysxlogins_sid = sid FROM master.dbo.sysxlogins WHERE name = 'NorthDomain\Joe'
UPDATE sysusers SET sid = @sysxlogins_sid WHERE name = 'Joe'

Logins

On the other end there are logins.

Get info about Existing Logins

To find out what access a login has on your server you can run this command sp_helplogins.

sp_helplogins

This will display a list of databases the login has access to as well as other info about the login such as the SID.

Create New Login

If there is a user in your database and there is not a corresponding login on the server you can use the following commands to create the new login.

If you are creating a standard SQL Server login you would use this command sp_addlogin to create the login.

sp_addlogin

If you are creating a Windows authentication login use this command sp_grantlogin.

sp_grantlogin 'NorthDomain\Mary'

After you create the login you may need to link the appropriate database user to this new login using the steps above.

Set Default Database

In addition if you need to change the default database for a login use this command sp_defaultdb for both Windows or standard logins.

sp_defaultdb 'NorthDomain\Mary', 'master'

Recreating Logins

In some cases you may need to duplicate the entire login list from one server to another. This can be achieved manually by running the above commands or you can use the stored procedure that was developed by Microsoft (see link below). When you create the logins manually the original SID is not kept and a new SID is created as the login is being created, therefore you will need to link the logins and users for your database. See How to transfer logins and passwords between instances of SQL Server for more information.

Next Steps
  • Keep these commands handy when you need to restore databases to different servers
  • Create a stored procedure and embed some of this code to make the process simpler, especially if you need to do this on a periodic basis like recreating a test environment from a production server


Last Updated: 2006-09-15


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, October 31, 2012 - 5:04:09 AM - Ravi Back To Top

Nice article Greg !! Really helpful !!


Learn more about SQL Server tools