By: Greg Robidoux | Comments (1) | Related: > 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.
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
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
