SQL 2008 Reporting services – ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
Had some strange error accessing the history tab and some of the other tabs of some scheduled reports in SQL 2008 SP1.
With a misleading error “ ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.”
I at one time restored these databases after a server reinstall from a failed SP1 upgrade. Everything worked for the most part but these odd errors on some reports kept happening.
This error pointed me in the right direction when expanding the schedules tab in SSMS connected with Reporting services
EXECUTE permission denied on object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’.
To resolve this ensure the rsexecrole exists in Master, MSDB, ReportServer & ReportServerTempDB
then run the following script
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
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
