Moving SQL Server Logins Between Servers
A very common challenge for a SQL Server DBA is moving SQL Server logins between servers. When you recreate a SQL Server log in (not a Windows log in), you get a new security ID (SID) by default, even though you have the same user name and password.
CREATE DATABASE LoginTest; GO CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd'; GO USE LoginTest; GO CREATE USER GregTest FROM LOGIN GregTest; GO USE master; GO
EXEC sp_detach_db 'LoginTest'; GO DROP LOGIN GregTest; GO CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd'; GO
CREATE DATABASE LoginTest ON (FILENAME = 'C:\SQLData\Data\LoginTest.mdf') FOR ATTACH; GO
USE LoginTest; GO CREATE USER GregTest FOR LOGIN GregTest; GO Msg 15023, Level 16, State 1, Line 1 User, group, or role 'GregTest' already exists in the current database.
EXEC sp_change_users_login 'Report'; GO UserName UserSID ---------------------------------------------- GregTest 0x5D5F9089AFE1D4428106DE1B52BE0DFC
sp_change_users_login then offers an option to fix it . The way sp_change_users_login fixes this issue is to update the SID in the database user to match the log in:
EXEC sp_change_users_login 'Update_One', 'GregTest', 'GregTest'; GO
ALTER USER GregTest WITH LOGIN = GregTest; GO
A workaround for this problem is to specify the SID value when creating the log in in TSQL. It is an optional parameter. If you provide the same value as on the other server, you don't have the problem. For example, instead of executing sp_change_users_login or ALTER USER as in the previous example, we could have done the following:
USE LoginTest; GO SELECT sid FROM sysusers WHERE name = 'GregTest'; GO sid ----------------------------------- 0x5D5F9089AFE1D4428106DE1B52BE0DFC (1 row(s) affected)
CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd', SID = 0x5D5F9089AFE1D4428106DE1B52BE0DFC; GO
This wiki article was adapted from a blog post by Greg Low.
Greg is an internationally recognised consultant, developer and trainer. He has been working in development since 1978, holds a PhD in Computer Science and a host of Microsoft certifications. Greg is the country lead for Solid Quality, a SQL Server MVP and one of only three Microsoft Regional Directors for Australia. Greg also hosts the popular SQL Down Under podcast (www.sqldownunder.com), organises the SQL Down Under Code Camp and co-organises CodeCampOz. He is a board member of PASS (the Professional Association for SQL Server). He speaks regularly at SQL Server events around the world.
His online presences include:
For more information about SQL Server logins and SIDs, check out these articles: