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...
Batch Script to Deploy Multiple SQL files (Version 2)
I took very long break to keep the site up to date with my learning. Few years back I wrote an article to deploy multiple scripts using batch fi...
Provisioning Azure Cosmos DB using Powershell
Azure Cosmos database is one of the NoSQL database that is available in Microsoft Azure cloud platform. Azure Cosmos db is getting more popular c...
powered by RelatedPosts