Orphaned Users Search and Destroy
This script will find all orphaned users on all databases on an instance, and generate a script to drop each user. Simply execute, copy the values from the DropScript column to a new SSMS window and execute.
There are two risks with this script. Use with caution:
- This script attempts to take into account users without logins. There is a list of specific database users excluded. The list may grow so be ready to add new login name values after line 47.
- Be cautious when running the DropScript on WINDOWS_USERS. They may be assigned permissions to a database because they are members of a WINDOWS_GROUP.
---------------------------------------------------------------------------------------------------
--Author: Patrick Slesicki
--Date: 06/26/2018
--Purpose: To find orphans on an instance and generate a script to drop them.
--Notes: Use with caution. MS has users without logins for some databases.
--History:
--mm/dd/yyyyInitDescription
-------------------------------------------------------------------------------------------
--03/06/2020PLSMajor revision using sids as join fields rather than names.
---------------------------------------------------------------------------------------------------
DECLARE @SQL AS nvarchar(2000) =
N'USE [?]
INSERT INTO #Orphan
(
DBName
,IsReadOnly
,UserName
,UserType
,DropScript
)
SELECT
DB_NAME()
,(SELECT is_read_only FROM sys.databases WHERE name = DB_NAME())
,dp.name
,dp.type_desc
,CASE
WHEN (SELECT is_read_only FROM sys.databases WHERE name = DB_NAME()) = 0 THEN ''USE '' + QUOTENAME(DB_NAME()) + ''; DROP USER '' + QUOTENAME(dp.name) + '';''
WHEN (SELECT is_read_only FROM sys.databases WHERE name = DB_NAME()) = 1
THEN ''USE master; ALTER DATABASE ''
+ QUOTENAME(DB_NAME()) + '' SET READ_WRITE WITH NO_WAIT; USE ''
+ QUOTENAME(DB_NAME()) + ''; DROP USER '' + QUOTENAME(dp.name)
+ ''; USE master; ALTER DATABASE '' + QUOTENAME(DB_NAME()) + '' SET READ_ONLY WITH NO_WAIT;''
ELSE NULL
END
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE
dp.principal_id > 4
AND dp.type IN (''G'', ''S'', ''U'')
AND dp.name NOT IN(
''MS_DataCollectorInternalUser''
,''AllSchemaOwner''
,''vssfadmin''
,''TFSWITDDLADMIN''
,''smsdbuser_ReadOnly''
,''smsdbuser_ReadWrite''
,''smsdbuser_ReportSchema''
)
AND dp.name NOT LIKE ''NT %''
AND sp.sid IS NULL;
';
---------------------------------------------------------------------------------------------------
--Drop the temp table if it exists and create the temp table
---------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#Orphan') IS NOT NULL
DROP TABLE #Orphan;
CREATE TABLE #Orphan
(
DBName nvarchar(128)
,IsReadOnly bit
,UserName nvarchar(128)
,UserType nvarchar(60)
,DropScript nvarchar(4000)
);
---------------------------------------------------------------------------------------------------
--Execute the dynamic sql statement
---------------------------------------------------------------------------------------------------
EXEC sys.sp_MSforeachdb @command1 = @SQL;
---------------------------------------------------------------------------------------------------
--Get results
---------------------------------------------------------------------------------------------------
SELECT
DBName
,IsReadOnly
,UserName
,UserType
,DropScript
FROM #Orphan
ORDER BY
DBName
,UserName;
---------------------------------------------------------------------------------------------------
--Cleanup
---------------------------------------------------------------------------------------------------
DROP TABLE #Orphan;
GO 
