create-ssrs-exec-role.sql

USE master
GO
DECLARE @RoleName VARCHAR(50) = 'RSExecRole';
DECLARE @Script VARCHAR(MAX) = 'USE master;';
SELECT @Script = @Script + 'CREATE ROLE ' + @RoleName + CHAR(13);
SELECT @Script
    = @Script + 'GRANT ' + prm.permission_name + ' ON ' + +QUOTENAME(OBJECT_SCHEMA_NAME(major_id)) + '.'
      + QUOTENAME(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;
GO 
USE msdb;
GO
DECLARE @RoleName VARCHAR(50) = 'RSExecRole';
DECLARE @Script VARCHAR(MAX) = 'USE msdb;';
SELECT @Script = @Script + 'CREATE ROLE ' + @RoleName + CHAR(13);
SELECT @Script
    = @Script + 'GRANT ' + prm.permission_name + ' ON ' + +QUOTENAME(OBJECT_SCHEMA_NAME(major_id)) + '.'
      + QUOTENAME(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;