Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008
Posted by Jeroen Pluimers on 2012/01/12
When moving databases across servers, you face the same problem as when upgrading servers: users are server specific, but permissions are databases specific. They are not bound by UserName, but through a SID (security identifier).
When adding the same UserName entries to a new server that already has the databases restored, you get error messages like these:
When adding the same UserName entries, then restoring the databases, it won’t work, because the matching SIDs don’t exist.
But sp_change_users_login (and the ALTER USER UserName WITH LOGINI = UserName2 in SQL Server 2005 and higher) is not the actual fix to the problem: it will re-add the user with a new SID, then correct the SID in the database currently in use.
Actually you are after “Orphaned” users: users that are defined in the various databases, but not present in the user list on the SQL Server instance. Running sp_change_users_login with the “Report’ parameter on the “new” server will show a list of orphaned users.
An even better way to show Orphaned Users is by running the ShowOrphanUsers script (thanks Vyas, wish more people republish your stuff with attribution!). In my own version (sourcecode is below), I have added an extra UserSID column of type varbinary(85).
In order to transfer users to a new server, you need a sp_help_revlogin stored procedure. Depending on the version of your SQL Server (7/2000/2005/2008), you need a slightly different version of a script that creates sp_help_revlogin for you. All versions are available at NetNerds.net.
USE master; GO IF OBJECT_ID ('dbo.ShowOrphanUsers') IS NOT NULL DROP PROCEDURE dbo.ShowOrphanUsers GO CREATE PROC dbo.ShowOrphanUsers AS BEGIN CREATE TABLE #Results ( [Database Name] sysname COLLATE Latin1_General_CI_AS, [Orphaned User] sysname COLLATE Latin1_General_CI_AS, [UserSID] varbinary(85) ) SET NOCOUNT ON DECLARE @DBName sysname, @Qry nvarchar(4000) SET @Qry = '' SET @DBName = '' WHILE @DBName IS NOT NULL BEGIN SET @DBName = ( SELECT MIN(name) FROM master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'tempdb', 'msdb', 'distribution', 'pubs', 'northwind' ) AND DATABASEPROPERTY(name, 'IsOffline') = 0 AND DATABASEPROPERTY(name, 'IsSuspect') = 0 AND name > @DBName ) IF @DBName IS NULL BREAK SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name], CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User], SID AS [UserSID] FROM ' + QUOTENAME(@DBName) + '..sysusers su WHERE su.islogin = 1 AND su.name <> ''guest'' AND NOT EXISTS ( SELECT 1 FROM master..sysxlogins sl WHERE su.sid = sl.sid )' INSERT INTO #Results EXEC (@Qry) END SELECT * FROM #Results ORDER BY [Database Name], [Orphaned User] END GO