Identify Orphan Users in All the databases

There are lot of scripts available in the internet to find orphan users across the database.
This will be my version fo script to find out orphan users in all the databases excluding system databases.

Applies to

SQL Server 2005 and Above

[sql]
DECLARE @OrphanUser TABLE( DatabaseName VARCHAR(255),
UserName VARCHAR(255),
DropScript VARCHAR(1024));
DECLARE @SQL VARCHAR(MAX);
IF( SELECT SUBSTRING(CONVERT( VARCHAR(25), SERVERPROPERTY(‘ProductVersion’)), 1, CHARINDEX(‘.’, CONVERT(VARCHAR(25), SERVERPROPERTY(‘ProductVersion’))) – 1)) < 12
BEGIN
SET @SQL = ‘SELECT ”?” [DatabaseName],a.name,”USE [?]; DROP LOGIN [”+a.name+”];”
FROM ?.sys.database_principals a
LEFT OUTER JOIN sys.server_principals b on a.sid=b.sid
WHERE a.type = ”S”
AND a.name NOT IN (”dbo”,”sys”,”INFORMATION_SCHEMA”,”guest”) AND a.name NOT LIKE ”%##%” AND DB_ID(”?”) > 4
AND b.sid IS NULL’;
END;
ELSE
BEGIN
SET @SQL = ‘SELECT ”?” [DatabaseName],a.name,”USE [?]; DROP LOGIN [”+a.name+”];”
FROM ?.sys.database_principals a
LEFT OUTER JOIN sys.server_principals b on a.sid=b.sid
WHERE a.type = ”S”
AND a.name NOT IN (”dbo”,”sys”,”INFORMATION_SCHEMA”,”guest”) AND a.name NOT LIKE ”%##%” AND DB_ID(”?”) > 4
AND b.sid IS NULL AND a.authentication_type=1’;
END;
INSERT INTO @OrphanUser
EXEC sp_MSforeachdb
@SQL;
SELECT *
FROM @OrphanUser
ORDER BY DatabaseName;
[/sql]