Moving SQL Server Logins Between Servers

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Database Administration - Security - Managing Logins


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.


Issues arise with SIDs when you restore a database from another server. You can't access the database. If you try to create the user entry in the database, you get an error message that says it already exists and fails. But if you try to list the users in the database, the list doesn't show up.


Here is an example below. First create a database and a login and add the user to the database: 

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


Next, detach the database, then drop and recreate the log in: 

EXEC sp_detach_db 'LoginTest';
GO
DROP LOGIN GregTest;
GO
CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';
GO


If we reattach the database, we now have a new login with a SID that’s different from the one the user has in the database, even though the user has the same name. This is similar to what happen when you restore a database on another server and recreate the log in there: 

CREATE DATABASE LoginTest 
ON (FILENAME = 'C:\SQLData\Data\LoginTest.mdf')
FOR ATTACH;
GO


If we try to use the log in to access the database or create the log in, neither will work: 

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.


The standard resolution for this has been to use sp_change_users_login. It has an option to list any mismatched log ins and database users -- those with the same names but different SIDs. 

 
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


In Service Pack 2 of SQL Server 2005, new syntax was introduced to deal with this : 

ALTER USER GregTest WITH LOGIN = GregTest;
GO


There are potential issues with this since it temporarily fixes the problem or at worst, propagates it to other servers. It's not the database SID that needs fixing; it's the log in's SID. If the log in's SID were correct, there wouldn't be a problem with copying the databases around. Here are couple common scenarios:

  1. A database is restored from another server (or a reinstalled server).
  2. The log ins that use the database need to be recreated.

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)


What we could then have done was: 

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',
  SID = 0x5D5F9089AFE1D4428106DE1B52BE0DFC;
GO


The upside of this is that it’s a permanent fix. Next time you restore the database, you won’t have to fix it again.


Author Credits

Greg Low

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:

Related Reading

For more information about SQL Server logins and SIDs, check out these articles:

Retrieved from "http://sqlserverpedia.com/wiki/Moving_SQL_Server_Logins_Between_Servers"