Fixed: The EXECUTE permission was denied on object xp_sqlagent_notify

Error Message: The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’.

Error Reason: This problem occurred due to the insufficient permission to Report Server database Role (RSExecRole) when we are creating new subscription in SSRS report.

Solution: We need to grant few permissions to report server database role (RSExecRole) to create a job and schedule the job for subscription notification. When we are creating new subscription in SSRS report that is actually creating a SQL job on report server database SQL instance and this error is occurring insufficient permissions to report server database role (RSExecRole).

Reporting Services uses a predefined database role called RSExecRole to grant report server permissions to the report server database. 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.

First of all, we should identify the report server database name and SQL instance using Report service configuration manager. The reason, sometimes we may use different SQL instance for the report server database based on the server and license availability.

Step 1: Go to Reporting Services Configuration Manager using start menu.

Step 2: Click Database option from Left Panel and get the SQL Server Name which shown in below.

Step 3: Connect the SQL Server using SQL Server Management Studio (SSMS).

Step 4: Click New Query from the Tool menu, paste below query and execute it.

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

This query will grant the permission to report server database role (RSExecRole) to create a subscription job and schedule it. Now, you just try to create a new subscription on SSRS report and hope you can do it.

Also see below articles

SQL Server/Developer Interview Questions and Answers for Experienced Professional
Difference between ROW_NUMBER, RANK, DENSE_RANK and NTILE in SQL Server