RSExecRole script

There are some roles that should come with Reporting Services.  But when one simply restores the RS databases to a new server, these roles are not automatically created in master and msdb.   There is a head-scratcher of an article on msdn, describing how to add these through Management Studio.  It just takes a few seconds to run a script.   I found out later that the script is actually at the bottom of that article in the community section.

Anyway, here is a script

USE [master]
GO
CREATE ROLE [RSExecRole] AUTHORIZATION [dbo];
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];

USE [msdb]
GO
CREATE ROLE [RSExecRole] AUTHORIZATION [dbo];
GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_help_job] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole];
GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole];
GRANT SELECT ON [dbo].[sysjobs] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_verify_job_identifiers] TO [RSExecRole];
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [RSExecRole];

Advertisements
Aside | This entry was posted in Database Administration and tagged . Bookmark the permalink.

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