The Wiert Corner – irregular stream of stuff

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

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

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

    Join 1,329 other followers

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:

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 NetNerds.net.

–jeroen

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

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

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 1,329 other followers

%d bloggers like this: