Recently we have been doing a number of instance moves as part of a large upgrade project. And as anyone who’s done many of these types of moves knows, orphans are a big pain. You backup your database from one server and restore it to another and all of a sudden no one can log in. Multiple that by ten, twenty, a hundred, and this can be a real problem. There are easy ways to avoid this but they don’t always work, mistakes are made etc. So in order to make my life a lot easier I created this script to clean up any orphans that were created by the move across the entire instance. I decided to post it to add it to my own library and in the hope that someone other than me will find it useful. So here it is:

--------------------------------------------------------------
--------------------------------------------------------------
-- Clean up orphans across an instance
--------------------------------------------------------------
--------------------------------------------------------------
-- Steps this script takes:
--    - Create a temp table containing all of the orphans from 
--      every database except for partialy contained databases.
--    - Attempt to create any missing NT authenticated logins
--        - If there is a \ in the name attempt to create the 
--          login.
--        - If there is no \ in the name add the @domain 
--          variable to the name and attempt to create the 
--          login.
--    - If the create for the NT authenticated login fails
--      save the error in the error column.
--    - If there is no error in the error column then attempt
--      to associated the orphaned user with a login of the 
--      of the same name.
--    - If there is an error then save it to error column. 
--    
--    - Output
--      - Number of orphans fixed and number of orphans 
--		  remaining.
--      - A list of all orphans
--
-- There are some limitations on what this script can do.  If
-- for example the orphaned user has a different name from the
-- associated login there isn't much I can do.
--------------------------------------------------------------
--------------------------------------------------------------


-- If temp table exists drop it
IF Object_Id('tempdb..#Orphans') IS NOT NULL
	DROP TABLE #Orphans
GO

-- Create new temp table
CREATE TABLE #Orphans ([dbname] sysname, [username] sysname, 
	[type] char(1), [error] varchar(4000),
	PRIMARY KEY (dbname, username))

-- This is the name of the main domain connecting to the instance.
-- This will be used as a first pass attempt to create windows
-- logins for orphans of type U (windows user) or G (windows group)
-- that don't already have a domain in the name.
DECLARE @domain varchar(200)
SET @domain = 'MyDomainName'

--------------------------------------------------------------
-- Load temp table with a list of orphans from each database
---- If contained databases is an option skip any database
---- that has a containment other than 'none'.
--------------------------------------------------------------
DECLARE @sql nvarchar(4000)

SET @sql =
	'DECLARE DBList CURSOR				' + CHAR(13) + 
	'READ_ONLY							' + CHAR(13) + 
	'FOR SELECT name FROM sys.databases ' + CHAR(13) + 
	'	WHERE name NOT IN (''tempdb'')	' + CHAR(13) + 
	'	  AND state = 0					' + CHAR(13) + 
	'	  AND is_read_only = 0			'

IF EXISTS (SELECT * FROM sys.all_columns WHERE name = 'containment' 
			AND object_id = object_id('master.sys.databases'))
	SET @sql = @sql + CHAR(13) + 
	'	  AND containment = 0			'  

EXEC sp_executesql @sql

DECLARE @dbname nvarchar(400)
OPEN DBList

FETCH NEXT FROM DBList INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SET @sql = 	'USE '+quotename(@dbname)+';  ' + char(13) + 
			' INSERT INTO #Orphans  (dbname, username, type)' + char(13) +   
			' SELECT '+quotename(@dbname,'''')+' AS dbname, name, type ' + char(13) +
			' FROM sys.database_principals ' + char(13) + 
			'	WHERE sid NOT IN (SELECT sid FROM sys.server_principals) ' + char(13) + 
			'	AND type IN (''S'',''G'',''U'') ' + char(13) + 
			'	AND LEN(sid) > 5 ' + char(13) + 
			'	AND name <> ''dbo'' '
		
		EXEC sp_executesql @sql
	END
	FETCH NEXT FROM DBList INTO @dbname
END

CLOSE DBList
DEALLOCATE DBList

--------------------------------------------------------------
---- Try to create any missing windows users
--------------------------------------------------------------
DECLARE OrphanList CURSOR
READ_ONLY
FOR SELECT DISTINCT 
		CASE WHEN username LIKE '%\%' THEN username
			ELSE @domain + '\' + username END
	FROM #Orphans 
	WHERE type IN ('G','U')

DECLARE @username nvarchar(400)
OPEN OrphanList

FETCH NEXT FROM OrphanList INTO @username
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		IF NOT EXISTS (SELECT 1 FROM sys.server_principals
							WHERE name = @username)
		BEGIN
			SET @sql = 'CREATE LOGIN ' + quotename(@username) + ' FROM WINDOWS '
			BEGIN TRY
				EXEC sp_executesql @sql
				-- PRINT @sql
			END TRY
			BEGIN CATCH
				UPDATE #Orphans SET error = ERROR_MESSAGE()
				WHERE username = @username
			END CATCH
		END
	END
	FETCH NEXT FROM OrphanList INTO @username
END

CLOSE OrphanList
DEALLOCATE OrphanList

--------------------------------------------------------------
---- Try to fix the orphans
--------------------------------------------------------------
DECLARE OrphanList CURSOR
READ_ONLY
FOR SELECT DISTINCT dbname, username, [type]
	FROM #Orphans 
	WHERE error IS NULL

-- DECLARE @dbname nvarchar(400)
-- DECLARE @username nvarchar(400)
DECLARE @type char(1)
DECLARE @loginname nvarchar(400)
OPEN OrphanList

FETCH NEXT FROM OrphanList INTO @dbname, @username, @type
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		IF @username NOT LIKE '%\%' AND @type IN ('U','G')
			SET @loginname = @domain + '\' + @username
		ELSE
			SET @loginname = @username

		IF NOT EXISTS (SELECT 1 FROM sys.server_principals
							WHERE [type] = @type
							  AND name = @loginname
						)
		BEGIN
			UPDATE #Orphans SET error = QUOTENAME(@loginname,'''') + ' does not have a  ' + 
					'corrisponding server principal. Either the database principal name does ' +
					'match the server principal name or the server principal needs to be created.'
				WHERE username = @username
		END
		ELSE
		BEGIN
			SET @sql = 'USE ' + quotename(@dbname) + '; ' + 
				'ALTER USER ' + quotename(@username) + ' WITH LOGIN = ' + quotename(@loginname)

			BEGIN TRY
				EXEC sp_executesql @sql
				-- PRINT @sql
			END TRY
			BEGIN CATCH
				UPDATE #Orphans SET error = ERROR_MESSAGE()
				WHERE username = @username
				  AND dbname = @dbname
			END CATCH
			END
	END
	FETCH NEXT FROM OrphanList INTO @dbname, @username, @type
END

CLOSE OrphanList
DEALLOCATE OrphanList

SELECT SUM(CASE WHEN error IS NULL THEN 1 ELSE 0 END) AS OrphansCleanedup,
	SUM(CASE WHEN error IS NOT NULL THEN 1 ELSE 0 END) AS RemainingOrphans
FROM #Orphans

SELECT * FROM #Orphans 
ORDER BY [dbname], CASE WHEN [error] IS NULL THEN 1 ELSE 0 END, [username]