-
Notifications
You must be signed in to change notification settings - Fork 6
Expand file tree
/
Copy pathSecurityAuditDatabaseUsers.sql
More file actions
430 lines (400 loc) · 17 KB
/
Copy pathSecurityAuditDatabaseUsers.sql
File metadata and controls
430 lines (400 loc) · 17 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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
--=============================================
-- Copyright (C) 2018 Raul Gonzalez, @SQLDoubleG
-- All rights reserved.
--
-- You may alter this code for your own *non-commercial* purposes. You may
-- republish altered code as long as you give due credit.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
-- TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--
-- =============================================
-- Author: Raul Gonzalez
-- Create date: 19/08/2013
-- Description: Returns all database users with database roles and permissions at object level
-- for the given database or all databases if not specified
--
-- Change Log: 20/09/2013 RAG - Modified the query to display permissions assigned to User defined database roles
-- 26/09/2013 RAG - Added parameter @includeSystemDBs to include or not System Databases
-- 21/01/2014 RAG - Added recordset with all database users with their roles
-- 25/03/2014 RAG - Added fuctionality to include permissions for user defined database roles
-- and to detect orphan users (without a login)
-- 29/02/2016 RAG - Added check for User schema to exist in order to generate a DROP SCHEMA statement
-- 15/03/2016 SZO - Added column granularity to REVOKE statements
-- 16/03/2016 SZO - Added column granularity to [permission_list] column
-- 24/01/2018 RAG - Added column [included_users] which will display all users that are member of a database role
-- Removed condition to exclude system database roles
-- 12/03/2020 RAG - Added column [CREATE_DB_ROLE]
-- 11/08/2020 RAG - Added parameter @onlyOrphanUsers to identify orphan users
-- 16/01/2021 RAG - Added parameter @EngineEdition
-- 30/04/2025 RAG - Added value X = External group from Microsoft Entra group or applications
-- Removed dependencies and solve collation issues to run on Azure SQL DB
-- Display 'CONTAINED USER' if that is the case
-- 25/07/2025 RAG - Added CREATE_USER column and other fixes
--
-- Params are concatenated to the @sqlstring string to avoid problems in databases with different collation than [DBA]
--
-- =============================================
DECLARE @dbname sysname = NULL;
DECLARE @db_principal_name sysname = NULL;
DECLARE @srv_principal_name sysname = NULL;
DECLARE @onlyOrphanUsers bit = 0;
DECLARE @includeSystemDBs bit = 1;
DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
-- =============================================
-- Do not modify below this line
-- unless you know what you are doing!!
-- =============================================
IF @EngineEdition = 5
BEGIN
-- Azure SQL Database, the script can't run on multiple databases
SET @dbname = DB_NAME();
END;
SET NOCOUNT ON;
DECLARE @countDBs int = 1,
@numDBs int,
@sqlstring nvarchar(MAX);
IF OBJECT_ID('tempdb..#databases') IS NOT NULL
DROP TABLE #databases;
IF OBJECT_ID('tempdb..#all_db_users') IS NOT NULL
DROP TABLE #all_db_users;
IF OBJECT_ID('tempdb..#all_db_permissions') IS NOT NULL
DROP TABLE #all_db_permissions;
CREATE TABLE #all_db_users (
database_id int,
principal_id int,
principal_sid varbinary(85),
principal_name sysname,
principal_type_desc nvarchar(60),
default_schema_name sysname NULL,
has_db_access bit,
authentication_type sysname,
database_roles nvarchar(MAX) NULL,
included_users nvarchar(MAX) NULL,
DROP_USER_SCHEMA nvarchar(MAX),
CREATE_DB_ROLE nvarchar(MAX),
DROP_DB_ROLE nvarchar(MAX),
DROP_DB_USER nvarchar(MAX),
CREATE_DB_USER nvarchar(MAX)
);
CREATE TABLE #all_db_permissions (
database_id int,
principal_sid varbinary(85),
principal_name sysname,
principal_type_desc sysname,
class_desc sysname NULL,
object_name sysname NULL,
permission_list nvarchar(512) NULL,
permission_state_desc sysname,
REVOKE_PERMISSION nvarchar(4000)
);
IF @dbname IS NOT NULL BEGIN
SET @includeSystemDBs = 1;
END;
SELECT IDENTITY(int, 1, 1) AS ID, name
INTO #databases
FROM sys.databases
WHERE state = 0
AND name LIKE ISNULL (@dbname, name)
AND (@includeSystemDBs = 1 OR database_id > 4);
SET @numDBs = @@ROWCOUNT;
WHILE @countDBs <= @numDBs BEGIN
SET @dbname = (SELECT name FROM #databases WHERE ID = @countDBs);
SET @sqlstring = CASE WHEN @EngineEdition <> 5 THEN N'USE ' + QUOTENAME(@dbname) ELSE '' END
+ N'
DECLARE @db_principal_name SYSNAME = '
+ ISNULL ((N'''' + @db_principal_name + N''''), N'NULL')
+ CONVERT (nvarchar(MAX), N'
DECLARE @numericVersion INT = CONVERT(INT, PARSENAME(CONVERT(SYSNAME, SERVERPROPERTY(''ProductVersion'')),4))
-- All database users and the list of database roles
INSERT INTO #all_db_users (
database_id
, principal_id
, principal_sid
, principal_name
, principal_type_desc
, default_schema_name
, has_db_access
, authentication_type
, database_roles
, included_users
, DROP_USER_SCHEMA
, CREATE_DB_ROLE
, DROP_DB_ROLE
, DROP_DB_USER
, CREATE_DB_USER
)
SELECT DB_ID() AS database_id
, dbp.principal_id AS principal_id
, dbp.sid AS principal_sid
, dbp.name AS principal_name
, dbp.type_desc AS principal_type_desc
, dbp.default_schema_name
, CASE
WHEN dp.state IN (''G'', ''W'') THEN 1
ELSE 0
END AS [has_db_access]
, authentication_type_desc
, STUFF((SELECT '', '' + dbr.name
FROM sys.database_principals dbr
LEFT JOIN sys.database_role_members AS drm
ON drm.member_principal_id = dbp.principal_id
WHERE dbr.principal_id = drm.role_principal_id
FOR XML PATH('''')),1,2,'''') AS database_roles
, STUFF((SELECT '', '' + USER_NAME(dbr.member_principal_id)
FROM sys.database_role_members AS dbr
WHERE dbr.role_principal_id = dbp.principal_id
FOR XML PATH('''')),1,2,'''') AS included_users
, CASE WHEN dbp.default_schema_name = dbp.name AND EXISTS (SELECT * FROM sys.schemas WHERE name = dbp.default_schema_name) THEN
''USE '' + QUOTENAME(DB_NAME()) + CHAR(10) + ''GO'' + CHAR(10) + ''DROP SCHEMA '' + QUOTENAME(dbp.default_schema_name) + CHAR(10) + ''GO'' + CHAR(10) +
''ALTER USER '' + QUOTENAME(dbp.name) + '' WITH DEFAULT_SCHEMA = [dbo]'' + CHAR(10) + ''GO''
ELSE ''''
END AS DROP_USER_SCHEMA
, (SELECT ''USE '' + QUOTENAME(DB_NAME()) + CHAR(10) + ''GO'' + CHAR(10) +
''CREATE ROLE '' + QUOTENAME(dbr.name) + CHAR(10) +
CASE WHEN @numericVersion >= 11
THEN ''ALTER ROLE '' + QUOTENAME(dbr.name) + '' ADD MEMBER '' + QUOTENAME(dbp.name)
ELSE '' EXECUTE sp_droprolemember '' + QUOTENAME(dbr.name) + '', '' + QUOTENAME(dbp.name)
END + CHAR(10) + ''GO'' + CHAR(10)
FROM sys.database_principals dbr
LEFT JOIN sys.database_role_members AS drm
ON drm.member_principal_id = dbp.principal_id
WHERE dbr.principal_id = drm.role_principal_id
FOR XML PATH('''')) AS CREATE_DB_ROLE
, (SELECT ''USE '' + QUOTENAME(DB_NAME()) + CHAR(10) + ''GO'' + CHAR(10) +
CASE WHEN @numericVersion >= 11
THEN ''ALTER ROLE '' + QUOTENAME(dbr.name) + '' DROP MEMBER '' + QUOTENAME(dbp.name)
ELSE '' EXECUTE sp_droprolemember '' + QUOTENAME(dbr.name) + '', '' + QUOTENAME(dbp.name)
END + CHAR(10) + ''GO'' + CHAR(10)
FROM sys.database_principals dbr
LEFT JOIN sys.database_role_members AS drm
ON drm.member_principal_id = dbp.principal_id
WHERE dbr.principal_id = drm.role_principal_id
FOR XML PATH('''')) AS DROP_DB_ROLE
, ''USE '' + QUOTENAME(DB_NAME()) + CHAR(10) +
''GO'' + CHAR(10) +
CHAR(9) + ''DROP USER '' + QUOTENAME(dbp.name) + CHAR(10) +
''GO'' AS DROP_DB_USER
, ''USE '' + QUOTENAME(DB_NAME()) + CHAR(10) +
''GO'' + CHAR(10) +
''IF DATABASE_PRINCIPAL_ID ('''' + QUOTENAME(dbp.name) + '''') IS NULL BEGIN '' + CHAR(10) +
CHAR(9) + ''CREATE USER '' + QUOTENAME(dbp.name) + CHAR(10) +
''END'' + CHAR(10) +
''GO'' AS CREATE_DB_USER
FROM sys.database_principals AS dbp
LEFT JOIN sys.database_permissions AS dp
ON dp.grantee_principal_id = dbp.principal_id
AND dp.type = ''CO''
WHERE ( dbp.type IN (''U'', ''S'', ''G'', ''C'', ''K'', ''X'', ''E'')
/* S = SQL user, U = Windows user, G = Windows group, C = User mapped to a certificate, K = User mapped to an asymmetric key, X = EXTERNAL_GROUP, E = EXTERNAL_USER */
AND dbp.name LIKE ISNULL(@db_principal_name, dbp.name) )
-- To get database roles
OR ( dbp.type IN (''R'')
AND dbp.name LIKE ISNULL(@db_principal_name, dbp.name)
-- AND dbp.principal_id < 16384
) -- db_owner
-- get a line per database user / object / permission state
;WITH users_with_permission AS (
SELECT DISTINCT
p.grantee_principal_id
, p.class
, CASE WHEN p.class = 1 THEN o.type_desc ELSE p.class_desc END AS class_desc
, p.major_id
, p.state
, p.state_desc
, dbp.principal_sid
, dbp.principal_name
, dbp.principal_type_desc
FROM sys.database_permissions AS p
INNER JOIN #all_db_users AS dbp
ON p.grantee_principal_id = dbp.principal_id
LEFT JOIN sys.objects AS o
ON o.object_id = p.major_id
WHERE dbp.database_id = DB_ID()
)
INSERT INTO #all_db_permissions (
database_id
, principal_sid
, principal_name
, principal_type_desc
, class_desc
, object_name
, permission_list
, permission_state_desc
, REVOKE_PERMISSION
)
SELECT
DB_ID() AS [database_id]
, p.principal_sid
, p.principal_name
, p.principal_type_desc
, p.class_desc
, CASE
WHEN p.class = 0 THEN ''DATABASE''
WHEN p.class = 1 THEN ''OBJECT::'' + QUOTENAME(OBJECT_SCHEMA_NAME(p.major_id)) + ''.'' + QUOTENAME(OBJECT_NAME(p.major_id))
WHEN p.class = 3 THEN ''SCHEMA::'' + QUOTENAME(sch.name)
WHEN p.class = 6 THEN ''TYPE::'' + QUOTENAME(SCHEMA_NAME(tt.schema_id)) + ''.'' + QUOTENAME(tt.name)
END AS [object_name]
, STUFF(
(SELECT
'', '' + per.[permission_name]
FROM (
--==== Because column updates create multiple ''UPDATE'' entries in the sys.database_permissions table,
-- there is a need to select distinct or group this table to remove them and only get a single row.
SELECT
DISTINCT
grantee_principal_id
, major_id
, CASE
WHEN minor_id > 0 THEN [permission_name] +
--==== Add the columns that the permission is granted on
-- specified to the [permission_list] columns
+ '' (''
+ STUFF(
(SELECT
'', '' + c.name
FROM sys.columns AS [c]
LEFT OUTER JOIN sys.database_permissions AS [col_dp]
ON c.[object_id] = col_dp.major_id
AND c.column_id = col_dp.minor_id
WHERE col_dp.grantee_principal_id = db_per.grantee_principal_id
AND col_dp.major_id = db_per.major_id
AND col_dp.[type] = db_per.[type]
FOR XML PATH('''')), 1, 1, '''') COLLATE DATABASE_DEFAULT
+ '' )''
ELSE [permission_name]
END AS [permission_name]
, class
, [state]
FROM sys.database_permissions as [db_per]) AS [per]
WHERE per.grantee_principal_id = p.grantee_principal_id
AND per.class = p.class
AND per.major_id = p.major_id
AND per.[state] = p.[state]
ORDER BY per.[permission_name] ASC
FOR XML PATH(''''))
, 1, 2, '''') AS [permission_list]
, p.state_desc
, STUFF(
(SELECT
CHAR(10) + ''USE '' + QUOTENAME(DB_NAME()) + CHAR(10) + ''GO'' + CHAR(10)
+ ''REVOKE '' + per.[permission_name]
+ ISNULL('' ON '' + ( CASE
WHEN p.class = 0 THEN ''DATABASE::'' + QUOTENAME(DB_NAME())
WHEN p.class = 1 THEN ''OBJECT::'' + QUOTENAME(OBJECT_SCHEMA_NAME(p.major_id)) + ''.'' + QUOTENAME(OBJECT_NAME(p.major_id))
WHEN p.class = 3 THEN ''SCHEMA::'' + QUOTENAME(SCHEMA_NAME(p.major_id))
WHEN p.class = 6 THEN ''TYPE::'' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + ''.'' + QUOTENAME(t.name)
-- TODO: Add joins to get those names
WHEN p.class = 5 THEN QUOTENAME(''Assembly'')
WHEN p.class = 10 THEN QUOTENAME(''XML Schema Collection'')
WHEN p.class = 15 THEN QUOTENAME(''Message Type'')
WHEN p.class = 16 THEN QUOTENAME(''Service Contract'')
WHEN p.class = 17 THEN QUOTENAME(''Service'')
WHEN p.class = 18 THEN QUOTENAME(''Remote Service Binding'')
WHEN p.class = 19 THEN QUOTENAME(''Route'')
WHEN p.class = 23 THEN QUOTENAME(''Full-Text Catalog'')
WHEN p.class = 24 THEN QUOTENAME(''Symmetric Key'')
WHEN p.class = 25 THEN QUOTENAME(''Certificate'')
WHEN p.class = 26 THEN QUOTENAME(''Asymmetric Key'')
ELSE NULL
END), '''')
--==== Permissions on columns
+ ISNULL('' (''
+ STUFF(
(SELECT
'', '' + c.name
FROM sys.columns AS [c]
LEFT OUTER JOIN sys.database_permissions AS [col_dp]
ON c.[object_id] = col_dp.major_id
AND c.column_id = col_dp.minor_id
WHERE col_dp.grantee_principal_id = per.grantee_principal_id
AND col_dp.major_id = per.major_id
AND col_dp.[type] = per.[type]
FOR XML PATH('''')), 1, 1, '''')
+ '' )'', '''')
+ '' TO '' + QUOTENAME(p.principal_name) + CHAR(10) + ''GO''
FROM (
--==== Because column updates create multiple ''UPDATE'' entries in the sys.database_permissions table,
-- there is a need to select distinct or group this table to remove them and only get a single row.
SELECT
DISTINCT
grantee_principal_id
, major_id
, [permission_name]
, class
, [state]
, [type]
FROM sys.database_permissions) AS [per]
LEFT JOIN sys.types AS t
ON t.user_type_id = per.major_id
WHERE per.grantee_principal_id = p.grantee_principal_id
AND per.class = p.class
AND per.major_id = p.major_id
AND per.[state] = p.[state]
ORDER BY per.[permission_name]
FOR XML PATH(''''))
, 1, 1, '''') AS [REVOKE_PERMISSION]
FROM users_with_permission AS p
LEFT JOIN sys.schemas AS sch
ON sch.schema_id = p.major_id
AND p.class = 3
LEFT JOIN sys.table_types AS tt
ON tt.user_type_id = p.major_id
-- To do, add joins to display info for each class
');
--SELECT @sqlstring
EXECUTE sp_executesql @sqlstring;
SET @countDBs = @countDBs + 1;
END;
SELECT DB_NAME (dbp.database_id) AS database_name
, dbp.principal_name
, dbp.principal_type_desc
, dbp.default_schema_name
, CASE WHEN dbp.has_db_access = 1 THEN 'Yes' ELSE 'No' END AS has_db_access
, CASE WHEN sp.name IS NULL AND dbp.authentication_type = 'DATABASE' THEN 'N/A' ELSE sp.name END AS login_name
, CASE WHEN sp.type_desc IS NULL AND dbp.authentication_type = 'DATABASE' THEN 'CONTAINED USER' ELSE sp.name END AS login_type
, dbp.database_roles
, ISNULL(dbp.included_users, '') AS included_users
, dbp.DROP_USER_SCHEMA
, dbp.CREATE_DB_ROLE
, dbp.DROP_DB_ROLE
, dbp.CREATE_DB_USER
, dbp.DROP_DB_USER
FROM #all_db_users AS dbp
LEFT JOIN sys.server_principals AS sp
ON sp.sid = dbp.principal_sid
WHERE ISNULL (sp.name, '') LIKE COALESCE (@srv_principal_name, sp.name, '')
AND (@onlyOrphanUsers = 0 OR
(dbp.principal_type_desc = 'SQL_USER'
AND sp.name IS NULL
AND dbp.principal_name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys'))
)
ORDER BY database_name ASC, dbp.principal_name ASC;
SELECT DB_NAME (dbp.database_id) AS database_name
, dbp.principal_name
--, dbp.principal_type_desc
--, sp.name AS login_name
--, sp.type_desc login_type
--, dbp.class_desc
, dbp.object_name
, dbp.permission_list
, dbp.permission_state_desc
, REPLACE(dbp.REVOKE_PERMISSION, 'REVOKE', 'GRANT') AS GRANT_PERMISSION
, dbp.REVOKE_PERMISSION
FROM #all_db_permissions AS dbp
LEFT JOIN sys.server_principals AS sp
ON sp.sid = dbp.principal_sid
WHERE ISNULL (sp.name, '') LIKE COALESCE (@srv_principal_name, sp.name, '')
AND (@onlyOrphanUsers = 0 OR
(dbp.principal_type_desc = 'SQL_USER'
AND sp.name IS NULL
AND dbp.principal_name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys'))
)
ORDER BY database_name, dbp.principal_name, dbp.class_desc, dbp.object_name;
GO