-
Notifications
You must be signed in to change notification settings - Fork 59
/
sp_AzSYNDBPermissions.sql
193 lines (180 loc) · 9.52 KB
/
sp_AzSYNDBPermissions.sql
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
/*********************************************************************************************
sp_AzSYNDBPermissions V1.0
Kenneth Fisher
http://www.sqlstudies.com
This stored procedure returns 3 data sets. The first dataset is the list of database
principals, the second is role membership, and the third is object and database level
permissions.
The final 2 columns of each query are "Un-Do"/"Do" scripts. For example removing a member
from a role or adding them to a role. I am fairly confident in the role scripts, however,
the scripts in the database principals query and database/object permissions query are
works in progress. In particular certificates, keys and column level permissions are not
scripted out. Also while the scripts have worked flawlessly on the systems I've tested
them on, these systems are fairly similar when it comes to security so I can't say that
in a more complicated system there won't be the odd bug.
Standard disclaimer: You use scripts off of the web at your own risk. I fully expect this
script to work without issue but I've been known to be wrong before.
Data is ordered as follows
1st result set: DBPrincipal
2nd result set: RoleName, UserName if the parameter @Role is used else
UserName, RoleName
3rd result set: ObjectName then Grantee_Name if the parameter @ObjectName
is used otherwise Grantee_Name, ObjectName
Because of complications when using Azure Synapse there are no parameters. This is
strictly all of the permissions in the database and all three outputs.
-- V1.0
-- 8/31/2020 – Create sp_AzSYNDBPermissions based on queries from sp_AzSQLDBPermissionss
*********************************************************************************************/
CREATE PROCEDURE dbo.sp_AzSYNDBPermissions
AS
-- Database Principals
SELECT DBPrincipals.principal_id AS DBPrincipalId, DBPrincipals.name AS DBPrincipal, DBPrincipals.type,
DBPrincipals.type_desc, DBPrincipals.default_schema_name, DBPrincipals.create_date,
DBPrincipals.modify_date, DBPrincipals.is_fixed_role,
Authorizations.name AS RoleAuthorization, DBPrincipals.sid,
CASE WHEN DBPrincipals.is_fixed_role = 0 AND DBPrincipals.name NOT IN ('dbo','guest', 'INFORMATION_SCHEMA', 'public', 'sys') THEN
'IF DATABASE_PRINCIPAL_ID(''' + DBPrincipals.name + ''') IS NOT NULL ' +
'DROP ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
WHEN 'K' THEN NULL
WHEN 'R' THEN 'ROLE'
WHEN 'A' THEN 'APPLICATION ROLE'
ELSE 'USER' END +
' '+QUOTENAME(DBPrincipals.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ';' ELSE NULL END AS DropScript,
CASE WHEN DBPrincipals.is_fixed_role = 0 AND DBPrincipals.name NOT IN ('dbo','guest', 'INFORMATION_SCHEMA', 'public', 'sys') THEN
'IF DATABASE_PRINCIPAL_ID(''' + DBPrincipals.name + ''') IS NULL ' +
'CREATE ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
WHEN 'K' THEN NULL
WHEN 'R' THEN 'ROLE'
WHEN 'A' THEN 'APPLICATION ROLE'
ELSE 'USER' END +
' '+QUOTENAME(DBPrincipals.name COLLATE SQL_Latin1_General_CP1_CI_AS) END +
CASE WHEN DBPrincipals.[type] = 'R' THEN
ISNULL(' AUTHORIZATION '+QUOTENAME(Authorizations.name COLLATE SQL_Latin1_General_CP1_CI_AS),'')
WHEN DBPrincipals.[type] = 'X' THEN ' FROM EXTERNAL PROVIDER'
WHEN DBPrincipals.[type] = 'A' THEN
''
WHEN DBPrincipals.[type] NOT IN ('C','K') THEN
ISNULL(' WITH DEFAULT_SCHEMA = '+
QUOTENAME(DBPrincipals.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS),'')
ELSE '' END +
CASE WHEN DBPrincipals.[type] = 'S'
THEN ', PASSWORD = ''<Insert Strong Password Here>'' ' ELSE '' END +
';'
AS CreateScript
FROM sys.database_principals DBPrincipals
LEFT OUTER JOIN sys.database_principals Authorizations
ON DBPrincipals.owning_principal_id = Authorizations.principal_id
WHERE 1=1
AND DBPrincipals.sid NOT IN (0x00, 0x01)
-- Database Role Members
SELECT Users.principal_id AS UserPrincipalId, Users.name AS UserName, Roles.name AS RoleName,
CASE WHEN Users.is_fixed_role = 0 AND Users.name <> 'dbo' THEN
'EXEC sp_droprolemember @rolename = '+QUOTENAME(Roles.name COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+', @membername = '+QUOTENAME(CASE WHEN Users.name = 'dbo' THEN NULL
ELSE Users.name END COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+';' END AS DropScript,
CASE WHEN Users.is_fixed_role = 0 AND Users.name <> 'dbo' THEN
'EXEC sp_addrolemember @rolename = '+QUOTENAME(Roles.name COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+', @membername = '+QUOTENAME(CASE WHEN Users.name = 'dbo' THEN NULL
ELSE Users.name END COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+';' END AS AddScript
FROM sys.database_role_members RoleMembers
JOIN sys.database_principals Users
ON RoleMembers.member_principal_id = Users.principal_id
JOIN sys.database_principals Roles
ON RoleMembers.role_principal_id = Roles.principal_id
WHERE 1=1
-- Database & object Permissions
; WITH ObjectList AS (
SELECT SCHEMA_NAME(sys.all_objects.schema_id) COLLATE SQL_Latin1_General_CP1_CI_AS AS SchemaName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS name,
object_id AS id,
'OBJECT_OR_COLUMN' AS class_desc,
'OBJECT' AS class
FROM sys.all_objects
UNION ALL
SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS AS SchemaName,
NULL AS name,
schema_id AS id,
'SCHEMA' AS class_desc,
'SCHEMA' AS class
FROM sys.schemas
UNION ALL
SELECT NULL AS SchemaName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS name,
principal_id AS id,
'DATABASE_PRINCIPAL' AS class_desc,
CASE type_desc
WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
WHEN 'DATABASE_ROLE' THEN 'ROLE'
ELSE 'USER' END AS class
FROM sys.database_principals
UNION ALL
SELECT NULL AS SchemaName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS name,
assembly_id AS id,
'ASSEMBLY' AS class_desc,
'ASSEMBLY' AS class
FROM sys.assemblies
UNION ALL
SELECT SCHEMA_NAME(sys.types.schema_id) COLLATE SQL_Latin1_General_CP1_CI_AS AS SchemaName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS name,
user_type_id AS id,
'TYPE' AS class_desc,
'TYPE' AS class
FROM sys.types
UNION ALL
SELECT NULL AS SchemaName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS name,
symmetric_key_id AS id,
'SYMMETRIC_KEYS' AS class_desc,
'SYMMETRIC KEY' AS class
FROM sys.symmetric_keys
UNION ALL
SELECT NULL AS SchemaName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS name,
certificate_id AS id,
'CERTIFICATE' AS class_desc,
'CERTIFICATE' AS class
FROM sys.certificates
UNION ALL
SELECT NULL AS SchemaName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS name,
asymmetric_key_id AS id,
'ASYMMETRIC_KEY' AS class_desc,
'ASYMMETRIC KEY' AS class
FROM sys.asymmetric_keys
)
SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, Grantor.name AS GrantorName,
Permission.class_desc, Permission.permission_name,
ObjectList.name AS ObjectName,
ObjectList.SchemaName,
Permission.state_desc,
CASE WHEN Grantee.is_fixed_role = 0 AND Grantee.name <> 'dbo' THEN
'REVOKE ' +
CASE WHEN Permission.[state] = 'W' THEN 'GRANT OPTION FOR ' ELSE '' END +
' ' + Permission.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +
CASE WHEN Permission.major_id <> 0 THEN ' ON ' +
ObjectList.class + '::' +
ISNULL(QUOTENAME(ObjectList.SchemaName),'') +
CASE WHEN ObjectList.SchemaName + ObjectList.name IS NULL THEN '' ELSE '.' END +
ISNULL(QUOTENAME(ObjectList.name),'')
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' ELSE '' END +
' FROM ' + QUOTENAME(Grantee.name COLLATE SQL_Latin1_General_CP1_CI_AS) + '; ' END AS RevokeScript,
CASE WHEN Grantee.is_fixed_role = 0 AND Grantee.name <> 'dbo' THEN
CASE WHEN Permission.[state] = 'W' THEN 'GRANT' ELSE Permission.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS END +
' ' + Permission.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +
CASE WHEN Permission.major_id <> 0 THEN ' ON ' +
ObjectList.class + '::' +
ISNULL(QUOTENAME(ObjectList.SchemaName),'') +
CASE WHEN ObjectList.SchemaName + ObjectList.name IS NULL THEN '' ELSE '.' END +
ISNULL(QUOTENAME(ObjectList.name),'')
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' ELSE '' END +
' TO ' + QUOTENAME(Grantee.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ' ' +
CASE WHEN Permission.[state] = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END +
' AS '+ QUOTENAME(Grantor.name COLLATE SQL_Latin1_General_CP1_CI_AS)+';' END AS GrantScript
FROM sys.database_permissions Permission
JOIN sys.database_principals Grantee
ON Permission.grantee_principal_id = Grantee.principal_id
JOIN sys.database_principals Grantor
ON Permission.grantor_principal_id = Grantor.principal_id
LEFT OUTER JOIN ObjectList
ON Permission.major_id = ObjectList.id
AND Permission.class_desc = ObjectList.class_desc
WHERE 1=1