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
| -- roles
SELECT 'CREATE ROLE [' + name + '] AUTHORIZATION dbo' AS create_role_statement
FROM sys.database_principals
WHERE type_desc = 'DATABASE_ROLE'
AND name NOT LIKE 'MS%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND name NOT IN ('public')
AND is_fixed_role = 0
ORDER BY name
-- users
SELECT 'CREATE USER [' + name + '] FOR LOGIN [' + name + ']' AS create_user_statement
FROM sys.database_principals
WHERE type_desc IN ('SQL_USER', 'WINDOWS_USER')
AND name NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')
ORDER BY name
-- role members
SELECT 'EXEC sp_addrolemember ''' + R.name + ''', ''' + M.name + '''' AS role_members_statement
FROM sys.database_role_members AS RM
INNER JOIN sys.database_principals AS R
ON R.principal_id = RM.role_principal_id
INNER JOIN sys.database_principals AS M
ON M.principal_id = RM.member_principal_id
WHERE R.name NOT LIKE 'MS%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND M.name <> 'dbo'
ORDER BY role_members_statement
-- role grants
;WITH
CTE AS
(
SELECT R.name AS role_name
, G.permission_name
, CASE G.class
WHEN 1 THEN O.name
WHEN 3 THEN S.name
WHEN 0 THEN DB_NAME()
END AS object_name
, CASE G.class
WHEN 1 THEN O.type_desc
WHEN 3 THEN 'SCHEMA'
WHEN 0 THEN 'DATABASE'
END AS object_type_desc
FROM sys.database_principals AS R
INNER JOIN sys.database_permissions AS G
ON G.grantee_principal_id = R.principal_id
LEFT JOIN sys.schemas AS S
ON G.major_id = S.schema_id
LEFT JOIN sys.objects AS O
ON G.major_id = O.object_id
WHERE R.name <> 'public'
AND G.permission_name <> 'CONNECT'
--ORDER BY R.name, object_name, G.permission_name
)
SELECT 'GRANT ' COLLATE database_default + permission_name
+ ' ON ' + CASE WHEN object_name IS NULL THEN 'SCHEMA::dbo TO [' ELSE object_name + ' TO [' END
+ role_name + ']' AS role_grant_statement
FROM CTE
-- user grants
;WITH
CTE AS
(
SELECT DB_USER.name AS user_name
, DB_GRANT.class_desc
, DB_GRANT.state_desc
, DB_GRANT.permission_name
, CASE DB_GRANT.class_desc
WHEN 'OBJECT_OR_COLUMN' THEN O.name
WHEN 'SCHEMA' THEN S.name
WHEN 'TYPE' THEN TY.name
ELSE O.name
END AS object_name
, O.type_desc AS DB_object_type
FROM sys.database_permissions AS DB_GRANT
INNER JOIN sys.database_principals AS DB_USER
ON DB_GRANT.grantee_principal_id = DB_USER.principal_id
LEFT JOIN sys.objects AS O
ON O.object_id = DB_GRANT.major_id
LEFT JOIN sys.types AS TY
ON TY.user_type_id = DB_GRANT.major_id
LEFT JOIN sys.schemas AS S
ON S.schema_id = DB_GRANT.major_id
WHERE DB_USER.name <> 'public'
AND DB_GRANT.permission_name <> 'CONNECT'
)
SELECT state_desc COLLATE database_default + ' ' + permission_name
+ CASE
WHEN object_name IS NULL THEN ''
ELSE ' ON ' + object_name
END + ' TO [' + user_name + ']' AS create_user_statement
FROM CTE
ORDER BY create_user_statement |
