Home Forums SQL Server 2008 SQL Server Newbies Replicating roles and role permissions from one database to another RE: Replicating roles and role permissions from one database to another

  • Found a solution for what I'm looking for:

    declare @DBRoleName varchar(40) = '[dbrolename]'

    SELECT 'GRANT ' + dbprm.permission_name + ' ON ' + OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) + ' TO ' + dbrol.name + char(13) COLLATE Latin1_General_CI_AS

    from sys.database_permissions dbprm

    join sys.database_principals dbrol on

    dbprm.grantee_principal_id = dbrol.principal_id

    where dbrol.name = @DBRoleName