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