/*----====----====----====----====----====----====----====----====

* 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
GO

SELECT 'USE <DatabaseName,string,Database Name>'
SELECT 'GO'
GO

SELECT
'EXEC dbo.sp_addrole @rolename = N''<RoleName,string,Role Name>'', @ownername = N''dbo''
GO
'

USE <DatabaseName,string,Database Name>
GO

 

SELECT 'GRANT ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO <RoleName,string,Role Name>'
FROM sys.database_permissions
WHERE grantee_principal_id in
(
SELECT principal_id FROM sys.database_principals
WHERE TYPE = 'R' and name = '<RoleName,string,Role Name>'
)
AND class > 0