-
Notifications
You must be signed in to change notification settings - Fork 51
/
Script DB level permissions.sql
194 lines (182 loc) · 7.81 KB
/
Script DB level permissions.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
194
-- Script DB level permissions
-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox
-- This script creates the SQL to create the role members for all roles on the database.
-- This is useful for scripting permissions in a development environment before refreshing development with a copy of production.
-- This will allow us to easily ensure development permissions are not lost during a prod to dev restoration.
/*
Author: S. Kusen
Updates:
05/14/2012: Incorporated a fix pointed out by aruopna for Schema-level permissions.
01/20/2010: Turned statements into a cursor and then using print statements to make it easier to
copy/paste into a query window.
Added support for schema level permissions
Thanks to wsoranno@winona.edu and choffman for the recommendations.
*/
/* ***************************************************** */
/* Script the role members for all roles on the database */
/* ***************************************************** */
DECLARE @sql VARCHAR(2048),
@sort INT;
DECLARE tmp CURSOR LOCAL FAST_FORWARD FOR
/*********************************************/
/********* DB CONTEXT STATEMENT *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB USER CREATION *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + rm.name + ''''
+ ')
BEGIN CREATE USER' + SPACE(1) + QUOTENAME(rm.name) + ' FOR LOGIN ' + QUOTENAME(rm.name)
+ ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(rm.default_schema_name) + SPACE(1)
+ 'END
ELSE BEGIN IF EXISTS (SELECT * from master.dbo.syslogins WHERE loginname = ' + SPACE(1) + '''' + rm.name + ''''
+ ') ALTER USER ' + QUOTENAME(rm.name) + ' WITH LOGIN = ' + QUOTENAME(rm.name) + ' ELSE IF ' + ''''
+ QUOTENAME(rm.name) + '''' + ' NOT IN (''[dbo]'',''[guest]'') DROP USER ' + QUOTENAME(rm.name) + ' END;' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE rm.type IN ( 'U', 'S', 'G' ) -- windows users, sql users, windows groups
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')
+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id)
--get user names on the database
IN
(
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
AND [type] IN ( 'G', 'S', 'U' ) -- S = SQL user, U = Windows user, G = Windows group
)
UNION
SELECT '' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN
perm.state_desc
ELSE
'GRANT'
END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.'
+ QUOTENAME(obj.name) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN
SPACE(0)
ELSE
'(' + QUOTENAME(cl.name) + ')'
END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id))COLLATE DATABASE_DEFAULT
+ CASE
WHEN perm.state <> 'W' THEN
SPACE(0)
ELSE
SPACE(1) + 'WITH GRANT OPTION'
END AS [-- SQL STATEMENTS --],
9 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl
ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
UNION
SELECT '' AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN
perm.state_desc --W=Grant With Grant Option
ELSE
'GRANT'
END + SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE DATABASE_DEFAULT --TO <user name>
+ CASE
WHEN perm.state <> 'W' THEN
SPACE(0)
ELSE
SPACE(1) + 'WITH GRANT OPTION'
END AS [-- SQL STATEMENTS --],
12 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
AND usr.principal_id > 4 -- 0 to 4 are system users/schemas
AND [usr].[type] IN ( 'G', 'S', 'U' ) -- S = SQL user, U = Windows user, G = Windows group
UNION
SELECT '' AS [-- SQL STATEMENTS --],
13 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN
perm.state_desc --W=Grant With Grant Option
ELSE
'GRANT'
END + SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + perm.class_desc + '::' COLLATE DATABASE_DEFAULT --TO <user name>
+ QUOTENAME(SCHEMA_NAME(perm.major_id)) + SPACE(1) + 'TO' + SPACE(1)
+ QUOTENAME(USER_NAME(perm.grantee_principal_id))COLLATE DATABASE_DEFAULT + CASE
WHEN perm.state <> 'W' THEN
SPACE(0)
ELSE
SPACE(1) + 'WITH GRANT OPTION'
END AS [-- SQL STATEMENTS --],
15 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN sys.schemas s
ON perm.major_id = s.schema_id
INNER JOIN sys.database_principals dbprin
ON perm.grantee_principal_id = dbprin.principal_id
WHERE perm.class = 3 --class 3 = schema
ORDER BY [-- RESULT ORDER HOLDER --];
OPEN tmp;
FETCH NEXT FROM tmp
INTO @sql,
@sort;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql;
FETCH NEXT FROM tmp
INTO @sql,
@sort;
END;
CLOSE tmp;
DEALLOCATE tmp;
GO
USE master;
GO