Foster Geng Foster Geng - 2 years ago 139
SQL Question

How to script SQL server database role?

I need to make a script to copy one particular database role from one SQL server to another.

Is there an easy way to generate a script that creates the role and all the role permissions?

Answer Source

You can get what you need with a script like this:

declare @RoleName varchar(50) = 'RoleName'

declare @Script varchar(max) = 'CREATE ROLE ' + @RoleName + char(13)
select @script = @script + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + + char(13) COLLATE Latin1_General_CI_AS 
from sys.database_permissions prm
    join sys.database_principals rol on
        prm.grantee_principal_id = rol.principal_id
where = @RoleName

print @script
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download