The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,880 other followers

Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008

Posted by jpluimers 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:

Error 21002 [SQL-DMO] User ‘account’ already exists

When adding the same UserName  entries, then restoring the databases, it won’t work, because the matching SIDs don’t exist.

There are many sites explaining the “Error 21002” and pointing to sp_change_users_login.

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


via: Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008.

USE master;
IF OBJECT_ID ('dbo.ShowOrphanUsers') IS NOT NULL
  DROP PROCEDURE dbo.ShowOrphanUsers
CREATE PROC dbo.ShowOrphanUsers
		[Database Name] sysname COLLATE Latin1_General_CI_AS,
		[Orphaned User] sysname COLLATE Latin1_General_CI_AS,
                [UserSID]  varbinary(85)


	DECLARE @DBName sysname, @Qry nvarchar(4000)

	SET @Qry = ''
	SET @DBName = ''

		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


		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 <> ''guest''
					SELECT 1
					FROM master..sysxlogins sl
					WHERE su.sid = sl.sid

		INSERT INTO #Results EXEC (@Qry)

	FROM #Results
	ORDER BY [Database Name], [Orphaned User]

One Response to “Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008”

  1. […] SQL Server 2000 and up, the easiest way to re-add a user that got orphaned is with a script like […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: