Sometimes, as a DBA, you will have to copy a database role to another instance, such as when you have to move something from development to testing or production. Recently, I discovered that using the SSMS “script as” option doesn’t help very much at all. It simply generates a statement to create the role but ignores all of the securables that the role has. So what you’d end up with, if you executed that script, is a role that did nothing. A lot of good that does…
After searching online for it, I couldn’t believe that such a simple script was not available. If it was then I sure wasn’t finding it.
Anyway, this script generates a script that adds a role to a database and then adds the associated securables. It consists only of SELECT statements and poses no risk of altering any data. The script that this script generates will only replicate what already existed.
Generate script to “script out” a role on a database.
For Best Results, set
RESULTS TO TEXT
Press Ctrl+Shift+m to fill in variables
SET NOCOUNT ON
SELECT ‘USE <DatabaseName,string,Database Name>’
‘EXEC dbo.sp_addrole @rolename = N”<RoleName,string,Role Name>”, @ownername = N”dbo”
USE <DatabaseName,string,Database Name>
SELECT ‘GRANT ‘ + permission_name + ‘ ON ‘ + OBJECT_NAME(major_id) + ‘ TO <RoleName,string,Role Name>’
WHERE grantee_principal_id in
SELECT principal_id FROM sys.database_principals
WHERE TYPE = ‘R’ and name = ‘<RoleName,string,Role Name>’
AND class > 0