I had this issue due to having the Reporting Services databases in an Availability Group. When we failed over (for the first time) there were a bunch of permissions not correctly set, specifically the RSExecRole was not there. The reports worked, but not the subscriptions and we couldn’t edit any of the report settings. Upon saving the subscription you see the error on the screenshot above.
In log file C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\LogFiles\ReportServerService__date_time.log
you will see the following:
|
1 2 |
library!WindowsService_55!1fdc!03/12/2022-08:00:02:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.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.; schedule!WindowsService_55!1fdc!03/12/2022-08:00:02:: i INFO: Error processing event with data: TimeEntered: 03/12/2022 08:00:02, Type: Event, EventType: TimedSubscription, SubscriptionID: 673faf5f-c816-4482-a61c-284f18242d5d. Error: Microsoft.ReportingServices.Diagnostics.Utilities.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. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'. |
Thus “The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’.”
In order to fix this log in to SQL database server where Reporting Server’s database resides and execute the following SQL commands:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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 |
Refer this article for more details.
Good luck!
