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

 

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;

Leave a Reply

Your email address will not be published. Required fields are marked *

*