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 |
Partager