Clone Role with Securables

From NazimWIKI
Jump to navigation Jump to search
declare @RoleName varchar(50) = '<ROLE_NAME>'
declare @Script varchar(max) = 'CREATE ROLE ' + @RoleName + char(13)

select @script = @script + 'GRANT ' + p.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + r.name + char(13) COLLATE Latin1_General_CI_AS
from sys.database_permissions p
   , sys.database_principals r 
where p.grantee_principal_id = r.principal_id
  and r.name = @RoleName

print @script