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]

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, Upgrades Tagged: microsoft sql server, orphans, security, upgrades