View Orphaned Users

The single-database half of the orphan hunt. This query left-joins sys.database_principals to sys.server_principals on SID and returns every SQL or Windows user whose login no longer exists on the server - with a ready-to-run USE/DROP USER statement next to each one.

SELECT
	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