Create the RSExecRole
Reporting Services uses a predefined database role called RSExecRole to grant report server permissions to the report server database. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role. However, when you move a report server database to a new or different SQL Server Database Engine, you must re-create the role in the Master and MSDB system databases.
Use the following instructions to perform the following steps:
Create and provision the RSExecRole in the Master system database.
Create and provision the RSExecRole in the MSDB system database.
Note
The instructions in this topic are intended for users who do not want to run a script or write WMI code to provision the report server database. If you manage a large deployment and will be moving databases routinely, you should write a script to automate these steps. For more information, see Access the Reporting Services WMI Provider.
Before you start
Back up the encryption keys so that you can restore them after the database is moved. This step doesn't directly affect your ability to create and provision the RSExecRole, but you must have a backup of the keys in order to verify your work. For more information, see Back Up and Restore Reporting Services Encryption Keys.
Verify you're logged on as a user account that has sysadmin permissions on the SQL Server instance.
Verify SQL Server Agent service is installed and running on the instance of the Database Engine instance that you plan to use.
Attach the ReportServerTempDB and ReportServer databases. You aren't required to attach the databases to create the actual role, but they must be attached before you can test your work.
The instructions for manually creating the RSExecRole are intended to be used within the context of migrating a report server installation. Important tasks such as backing up and moving the report server database aren't addressed in this article, but are documented in the Database Engine documentation.
Create RSExecRole in master
Reporting Services uses extended stored procedures for SQL Server Agent service to support scheduled operations. The following steps explain how to grant Execute permissions for the procedures to the RSExecRole role.
Create RSExecRole in the master system database by using Management Studio
Start SQL Server Management Studio and connect to the Database Engine instance that hosts the report server database.
Open Databases.
Open System Databases.
Open Master.
Open Security.
Open Roles.
Right-click Database Roles, and select New Database Role. The Database Role - New page appears.
In Role name, enter RSExecRole.
In Owner, enter dbo.
Select page Securables.
Select Search. The Add Objects dialog box appears. The Specific Objects option is selected by default.
Select OK. The Select Objects dialog box appears.
Select Object Types.
Select Extended Stored Procedures.
Select OK.
Select Browse.
Scroll down the list and select the following procedures:
xp_sqlagent_enum_jobs
xp_sqlagent_is_starting
xp_sqlagent_notify
Select OK, and the select OK again.
In the Execute row, in the Grant column, select the check box.
Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all three stored procedures.
Select OK to finish.
Create RSExecRole in MSDB
Reporting Services uses stored procedures for SQL Server Agent service and retrieves job information from system tables to support scheduled operations. The following steps explain how to grant Execute permissions for the procedures and Select permissions on the tables to the RSExecRole.
Create RSExecRole in the MSDB system database
Repeat similar steps for granting permissions to stored procedures and tables in MSDB. To simplify the steps, you provision the stored procedures and tables separately.
Open MSDB.
Open Security.
Open Roles.
Right-click Database Roles, and select New Database Role. The General page appears.
In Role name, enter RSExecRole.
In Owner, enter dbo.
Select the Securables page.
Select Search. The Add Objects dialog box appears. The Specify Objects option is selected by default.
Select OK.
Select Object Types.
Select Stored Procedures.
Select OK.
Select Browse.
Scroll down the list of items and select the following stored procedures:
sp_add_category
sp_add_job
sp_add_jobschedule
sp_add_jobserver
sp_add_jobstep
sp_delete_job
sp_help_category
sp_help_job
sp_help_jobschedule
sp_verify_job_identifiers
Select OK, and then choose OK again.
Select the first stored procedure: sp_add_category.
In the Execute row, in the Grant column, select the checkbox.
Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all 10 stored procedures.
Still on the Securables page, select Search again. The Add Objects dialog box appears. The Specify Objects option is selected by default.
Select OK.
Select Object Types.
Select Tables.
Select OK.
Select Browse.
Scroll down the list of items and select the following tables:
syscategories
sysjobs
Select OK, and the select OK again.
Select the first table: syscategories.
In the Select row, in the Grant column, select the checkbox.
Repeat for the sysjobs table. RSExecRole must be granted Select permissions for both tables.
Select OK to finish.
Move the report server database
After you create the roles, you can move the report server database to new SQL Server instance. For more information, see Move the report server databases to another computer.
If you're upgrading the Database Engine to SQL Server 2016 or later, you can upgrade it either before or after moving the database.
The report server database is upgraded automatically when the report server connects to it. There are no specific steps required for upgrading the database.
Restore encryption keys and verify your work
If you attached the report server databases, you should now be able to complete the following steps to verify your work.
Verify report server operability after a database move
Start the Reporting Services Configuration tool and connect to the report server.
Select Database.
Select Change Database.
Select Choose an existing report server database.
Enter the server name of the Database Engine. If you attached the report server databases to a named instance, you must enter the instance name in this format: <servername>\<instancename>.
Select Test Connection. You should see a dialog box that states, "Test Connection Succeeded."
Select Ok to close the dialog box and then select Next.
On the Database, select the report server database.
Select Next and complete the wizard.
Select Encryption Keys.
Select Restore.
Select the strong file (.snk) that has the backup copy of the symmetric key used to decrypt stored credentials and connection information in the report server database.
Enter the password and select OK.
Select Web Portal URL.
Select the link to open the web portal. You should see the report server items from the report server database.
Create the RSExecRole role and permissions by using T-SQL
The role can also be created, and applicable permissions granted, on the system databases by using the following T-SQL script:
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_is_starting TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_notify TO [RSExecRole];
GO
USE msdb;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.sp_add_category TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobschedule TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobserver TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobstep TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_delete_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_category TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_jobschedule TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_verify_job_identifiers TO [RSExecRole];
GRANT SELECT ON dbo.syscategories TO [RSExecRole];
GRANT SELECT ON dbo.sysjobs TO [RSExecRole];
GO