-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathGetOrphanedUsers.sql
More file actions
143 lines (127 loc) · 5.95 KB
/
GetOrphanedUsers.sql
File metadata and controls
143 lines (127 loc) · 5.95 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
/*
Script: GetOrphanedUsers.sql
Description: Detects orphaned database users across all databases on a SQL Server instance.
An orphaned user is a database principal whose matching server-level login has
been deleted or no longer exists. This script is containment-aware: it correctly
excludes contained database users (authentication_type 2 = database password,
3 = database Windows) that most orphaned user scripts misidentify as orphans.
Uses sp_MSforeachdb to iterate all databases and collects results into a single
report via a global temp table.
Prerequisites: - SQL Server 2016+ (STRING_SPLIT requires 2016+ or compatibility level 130+;
sys.database_principals.authentication_type requires 2012+;
contained databases require 2012+)
- Execute from any database context (the script switches context per database)
- Permissions: VIEW ANY DATABASE and the ability to read sys.database_principals
in each target database (typically requires sysadmin, or db_owner / db_securityadmin
in each database, or VIEW DATABASE STATE)
- sp_MSforeachdb is an undocumented Microsoft system procedure; it is present in
all supported SQL Server versions but is not officially supported
Configuration: @ExcludeDatabases - comma-delimited list of databases to skip (default: none)
@IncludeSystemDatabases - whether to include system databases (default: 1 / yes)
Usage Notes: - This is a read-only, non-destructive reporting script. It does not modify
any users, logins, or permissions
- Safe to run in production at any time with minimal performance impact
- Results are returned as a SELECT and the temp table is cleaned up automatically
- To remediate orphaned users, review results and either re-map them to a login
using ALTER USER ... WITH LOGIN = ..., or drop them if no longer needed
License: MIT License - https://opensource.org/licenses/MIT
Source: https://github.com/mbentham/sql-server-scripts
*/
-- ============================================================================
-- CONFIGURATION
-- ============================================================================
-- Comma-delimited list of databases to exclude from the scan (e.g. N'StagingDB,TestDB')
-- Leave empty to scan all databases.
DECLARE @ExcludeDatabases NVARCHAR(MAX) = N'';
-- Set to 0 to skip system databases (master, model, msdb, tempdb).
-- Default is 1 (include system databases) since orphaned users in system
-- databases can indicate security hygiene issues.
DECLARE @IncludeSystemDatabases BIT = 1;
-- ============================================================================
-- MAIN SCRIPT
-- ============================================================================
SET NOCOUNT ON;
-- Parse the exclusion list into a table for lookups
IF OBJECT_ID('tempdb..#ExcludedDatabases') IS NOT NULL
DROP TABLE #ExcludedDatabases;
CREATE TABLE #ExcludedDatabases (
DatabaseName NVARCHAR(128) NOT NULL PRIMARY KEY
);
-- Populate exclusion list from the comma-delimited parameter
IF @ExcludeDatabases <> N''
BEGIN
INSERT INTO #ExcludedDatabases (DatabaseName)
SELECT LTRIM(RTRIM(value))
FROM STRING_SPLIT(@ExcludeDatabases, N',')
WHERE LTRIM(RTRIM(value)) <> N'';
END
-- Add system databases to exclusion list if configured
IF @IncludeSystemDatabases = 0
BEGIN
INSERT INTO #ExcludedDatabases (DatabaseName)
SELECT name
FROM (VALUES (N'master'), (N'model'), (N'msdb'), (N'tempdb')) AS sysdb(name)
WHERE name NOT IN (SELECT DatabaseName FROM #ExcludedDatabases);
END
-- Create the results table (idempotent)
IF OBJECT_ID('tempdb..##OrphanedUsers') IS NOT NULL
DROP TABLE ##OrphanedUsers;
CREATE TABLE ##OrphanedUsers (
[DatabaseName] NVARCHAR(128) NOT NULL,
[UserName] NVARCHAR(128) NOT NULL,
[UserType] NVARCHAR(60) NULL,
[CreateDate] DATETIME NULL,
[CollectionDateTime] DATETIMEOFFSET(7) NOT NULL
);
-- Iterate all databases and detect orphaned users
EXEC sp_MSforeachdb N'
IF DB_ID(''?'') IS NOT NULL
AND DATABASEPROPERTYEX(''?'', ''Status'') = ''ONLINE''
AND HAS_DBACCESS(''?'') = 1
AND ''?'' NOT IN (SELECT DatabaseName COLLATE database_default FROM #ExcludedDatabases)
BEGIN
USE [?];
INSERT INTO ##OrphanedUsers (DatabaseName, UserName, UserType, CreateDate, CollectionDateTime)
SELECT
DB_NAME() AS DatabaseName,
dp.name AS UserName,
dp.type_desc AS UserType,
dp.create_date AS CreateDate,
SYSUTCDATETIME() AS CollectionDateTime
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
ON dp.sid = sp.sid
CROSS APPLY (
SELECT containment
FROM sys.databases
WHERE name = DB_NAME()
) AS dc
WHERE sp.sid IS NULL
AND dp.type IN (''G'', ''S'', ''U'')
AND dp.name NOT IN (
''dbo'',
''guest'',
''INFORMATION_SCHEMA'',
''sys'',
''MS_DataCollectorInternalUser''
)
AND (
dc.containment = 0
OR dp.authentication_type NOT IN (2, 3)
);
END
';
-- Return results
SELECT
DatabaseName,
UserName,
UserType,
CreateDate,
CollectionDateTime
FROM ##OrphanedUsers
ORDER BY DatabaseName, UserName;
-- Cleanup
IF OBJECT_ID('tempdb..##OrphanedUsers') IS NOT NULL
DROP TABLE ##OrphanedUsers;
IF OBJECT_ID('tempdb..#ExcludedDatabases') IS NOT NULL
DROP TABLE #ExcludedDatabases;