Roles to be added while moving a Report Server Database

Penning down this today as I recently saw my friends struggling to connect with Reporting server after migrating the report server database.

Reporting Services uses a predefined DB role called RSExecRole to grant report server permissions to the report server DB. 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, must re-create the role in the Master and MSDB system databases.

After you create the roles, you can move the report server database to new SQL Server instance.

T-SQL for adding the roles explicitly within the new instance:

use [master]
GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_is_starting] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]
use [msdb]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_verify_job_identifiers] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [RSExecRole]
GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole]
GRANT SELECT ON [dbo].[sysjobs] TO [RSExecRole]

Happy learning!

Cheers!
Aman Kharbanda

Advertisements

Author: sqlserverposts

Database Consultant by profession. I am a geek and a avid sports lover too. "Progress informed by the past, inspired by future".

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s