Script to copy one particular database role from one SQL server to another

Share via:

Below Script is to copy one particular database role from one SQL server to another

declare @RoleName varchar(50) = ‘Database_Role_Name’

declare @Script varchar(max) = ‘CREATE ROLE ‘ + @RoleName + char(13)
select @script = @script + ‘GRANT ‘ + prm.permission_name + ‘ ON ‘ + OBJECT_NAME(major_id) + ‘ TO ‘ + rol.name + 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 rol.name = @RoleName

print @script

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (14 votes, average: 5.00 out of 5)
Loading...

3 thoughts on “Script to copy one particular database role from one SQL server to another

Add Comment