Error Viewing the Execution option of a Report

I select the option execution from a report (it's in properties option) and then raise an error which says An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

I have full privileges in the DB and I think the report server is well configured. This is happening since I reinstalled all the system (including windows). Any ideas?

I think you would need to grant permissions to SQL Agent stored procedures that Reporting services use. Here is the list from our installation 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

-- 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

|||I'm seeing that i'm not have RsExecRole. If there is something wrong with my installation of RS, or the RS DB? Any one have an idea if I have to create a SQL group and then run the script above?|||Yes, you have to create a role called RsExecRole in ReportServer, ReportServerTempDB, master and msdb.
You probably have it in ReportServer and ReportServerTempDB if you backed up your database and restored it on a new SQL server.

Thanks
Tudor|||Yeeeees. You were right. I created the roles in master and in msdb. Because it was already in RS DB (I restore those DB). After that I executed the script you gave me and that's all.

Thank you

VectorR3|||

Hi,

we have the same error for a scheduled subscription report. when we run the report normally it works

I applied the script but still getting the same error. any idea?

Thanks
Bava

|||I was unable to subscribe to reports and this solved my problem. Thanks for your help.

John Cutsinger
Altegris|||This also solved the problem for me. Thanks!

|||

Thanks for the post.. i got the same error and was able to solve it thanks to Tudor