Fullsix Blog

If you are in the hosting business, or if you just create and delete a lot of users from your SQL Server instance, you might have the need to periodically check if there are orphaned logins in your server. These logins don’t have any database user mapped, and if they don’t have any relevant server role assigned they can be safely removed if needed.

During a recent credential rotation maintenance on our hosting servers I intended to list any orphaned logins that could have been left behind for some reason:

USE MASTER;

CREATE TABLE #dbusers (

sid VARBINARY(85))

EXEC sp_MSforeachdb

'insert #dbusers select sid from [?].sys.database_principals where   type != ''R'''

SELECT name

FROM     sys.server_principals

WHERE    sid IN (SELECT sid

FROM   sys.server_principals

WHERE  TYPE != 'R'

AND name NOT LIKE   ('##%##')

AND name NOT LIKE   ('%\%')

AND name NOT LIKE ('NT   %')

EXCEPT

SELECT DISTINCT sid

FROM   #dbusers)

GO

DROP TABLE #dbusers

This script makes the assumption that the logins you are targeting are SQL logins, and not Windows logins (hence the ‘%\%’ term).