Clean up all (most) of the orphans on an instance

3

June 3, 2015 by Kenneth Fisher

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]

3 thoughts on “Clean up all (most) of the orphans on an instance

  1. Frank says:

    This is great. Thank you! I added ” AND state = 0″ to the sys.database query to skip my offline databases.

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

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

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: