I was surprised that SSMS does not allow the scripting of user defined database/application roles. Looked around the googlesphere and couldn’t find a script that would produce what my purpose required which is migration. The below script will provide you with the GRANT statement for your secured principles. All that remains, is a CREATE ROLE statement preceding the GRANT statements for the environment you’re migrating too. (CREATE ROLE BLUEBOOKapp [YourRoleName])

SELECT ‘GRANT ‘ + dp.permission_name + ‘ ON ‘ + OBJECT_NAME(major_id) + ‘ TO ‘ + rl.name + CHAR(13) COLLATE Latin1_General_CI_AS

FROM sys.database_permissions dp
JOIN sys.database_principals rl
ON dp.grantee_principal_id = rl.principal_id
WHERE rl.NAME = [YourRoleName]

Example of the T-SQL produced by the script.

Example of the T-SQL produced by the script.

Advertisements