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

Leave a comment

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

*


x

Related Posts

Retrieve Auditing Configuration Details
Script to retrieve auditing configuration details in sql server. Auditing feature is released from SQL Server 2008 onward so if you have utilized...
CSSUG October 2019 Meet
We are proud to share CSSUG is getting resumed after couple of years. All the hard work and initiation is done by Madhivanan. Thanks to Madhi fo...
Dropping a Subscriber from AG Publisher
Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same...
powered by RelatedPosts