Sunday 27 March 2011

Reporting Services - Missing Schedules after DR/migration

Issue & Troubleshooting
After restoring Reporting Services databases to a new SQL instance (and restoring Reporting Services app server from tape) the app support team could not edit subscriptions and subscriptions were not running to schedule.

- All security listed as pre-requisite in RS text had been setup correctly
- Reporting Services was not throwing up any errors
- Reports were visible from the reportserver link in explorer
- Reporting Services was connecting to the new database server (and databases) with no problem

No SQL Agent jobs had been created by reporting services on the new server.  I initially suspected the reports would need to be redeployed and that is when the subscriptions/schedules would be created on the new db server.

However, I found Microsoft text explaining that when a reporting services database is moved, subscriptions and schedules would be updated automatically when the reporting services was repointed to the new database. 

When I logged onto the Reporting Services GUI on the app server, the following error was thrown up:
EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'

Fix
Researched and added RSExec required system stored procedure permissions.
Script below.
USE master
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
GO
USE msdb
GO
-- Permissions for SQL Agent SP's
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GO

No comments:

Post a Comment