When you create a user (a database principal) you have several options on what the user is associated with (usually a login/server principal), or it might not be associated with anything at all (created without a login). And a common problem is when that object you’ve associated your user with is no longer available and you’ve got an orphan.

You can tell if a user might be an orphan easily enough.

SELECT * FROM sys.database_principals
WHERE sid NOT IN (SELECT sid FROM sys.server_principals)
  AND TYPE NOT IN ('A','R') ;

Roles and Application roles can’t be orphans so there is no point in checking them.

When I first started writing this post (almost a year ago) I had found a really interesting blog with a piece of code that used some calculations on the SID to tell if the database principal (user) should or should not have a server principal (login) associated with it. I can’t find it anymore . That’s what I get for not taking better notes. Fortunately, I found a better way.

SELECT name, type, authentication_type_desc FROM sys.database_principals;

There is a handy dandy column in sys.database_principals called authentication_type_desc. Per the documentation this is what’s in there:

• NONE : No authentication
• INSTANCE : Instance authentication
• DATABASE : Database authentication
• WINDOWS : Windows authentication
• EXTERNAL: Azure Active Directory authentication

It tells you where the id is going to be authenticated from. In very simplified terms where is the password. Windows: Active Directory/Windows, External: Azure Active Directory, Instance, Database: SQL Auth and None: THERE IS NO Authentication! So Roles, SQL Ids created without a login, etc. So simple query to get orphans:

SELECT dp.name 
FROM sys.database_principals dp
WHERE authentication_type_desc <> 'NONE'
  AND NOT EXISTS (SELECT 1 FROM sys.server_principals sp
					WHERE dp.sid = sp.sid);

I should point out I’m not taking into account partially contained databases and the possibility of having a user completely contained by the database. That might be the Database authenticated but I haven’t had time to do any testing on that yet. I’ll let you know .

--------------------------------------------------------------
--------------------------------------------------------------
-- 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]