View Orphaned Users on All Databases

The server-wide half of the orphan hunt. This script walks every database with a cursor, runs the orphaned-user query in each one via dynamic SQL, and collects the results into a single #OrphanUsers table showing the database, principal name, SID and a ready-to-run DROP USER statement for every orphan on the instance.

USE master;

DECLARE @tSQL nVarChar(Max)
DECLARE @Database nVarChar(128)

DECLARE AllDatabases CURSOR FOR
	SELECT
		name
	FROM
		sys.databases
OPEN AllDatabases

CREATE TABLE #OrphanUsers
(
	DatabaseName sysname null,
	PrincipalName sysname null,
	DatabasePrincipalSID varbinary(128) null,
	DropScript nvarchar(256)
)

FETCH FROM AllDatabases INTO @Database

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE

SET @tSQL =
'
USE [' + @Database + '];
INSERT INTO #OrphanUsers
(
	DatabaseName,
	PrincipalName,
	DatabasePrincipalSID,
	DropScript
)
SELECT
	db_name(),
	DatPrin.name AS [PrincipalName],
	DatPrin.[sid] AS [DatabasePrincipalSID],
	''USE ['' + db_name() + '']; DROP USER ['' + DatPrin.name + '']''
FROM
	sys.database_principals as DatPrin
LEFT OUTER JOIN  sys.server_principals AS SysPrin
	ON SysPrin.[sid] = DatPrin.[sid]
WHERE
	DatPrin.type_desc IN (''SQL_USER'', ''WINDOWS_USER'')
	AND DatPrin.name <> ''guest''
	AND DatPrin.name <> ''dbo''
	AND SysPrin.[sid] IS NULL
	AND DatPrin.[sid] IS NOT NULL
'

EXEC(@tSQL)

FETCH FROM AllDatabases INTO @Database
END--WHILE

CLOSE AllDatabases DEALLOCATE AllDatabases

SELECT * FROM #OrphanUsers

DROP TABLE #OrphanUsers