Greetings to the Kingdom,

Over the weekend we had a datacenter move and in the the process moved the database for SSRS.  I knew that my subscriptions would automatically move, so imagine my surprise when they didn’t.  Our SSRS databases were located on a new server that had the same name as the old server.  After running down the error “The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’”, I found a super awesome blog post.  It noted that I was missing assignments to my RSExecRole role.  Once I ran the script, I stopped and restart SSRS and all the subscriptions came over.  Huge sigh of relief.  Have a great day!

http://karthikeyandba.wordpress.com/2011/07/15/the-execute-permission-was-denied-on-the-object-xp_sqlagent_notify-database-mssqlsystemresource-schema-sys/#comment-35

This error will occur when you are trying to create subscriptions for a reports

Execute the below script will solve the issue.

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