/*
This script will script 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.
*/
/*********************************************/
/********* 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 ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
3 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 --],
3 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_role_members AS rm
WHEREUSER_NAME(rm.member_principal_id) IN (
--get user names on the database
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
)
--ORDER BY rm.role_principal_id ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_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 --],
5 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
--WHEREusr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
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
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
--WHEREusr.name = @OldUser
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
ORDER BY [-- RESULT ORDER HOLDER --]
I created this script to do a similar thing for database level permissions.
This is useful for scripting out access in development, restoring a database from production/staging into a development environment, and then running the output from this script, column 1, to restore permissions in development quickly and easily.
If anyone has any comments or if something else exists that does a similar function, it would be appreciated.
My use:
Developer has dbo rights in development, but no rights in production. When restoring the DB from production to development, their dbo rights are washed away. So before the restore, i script out the permissions, then run the output to restore their dbo rights.
Steve