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

|||

Hi. I am very much having this error, and it doesn't want to go away!

I have the RsExecRole role on all 4 the DB's. It was already there.

I ran your scripts, but still getting the error. I changed the DB owner of my ReportServer$SQL2005 and ReportServer$SQL2005TempDB DB's from my domain administrator to the sa user, which is the user I use in my report's datasource to connect to the DB. Still it doesn't work.

I am quite fedup with this thing now. Spent most of the day on it with no progress.

Any help / tips will be supremely appreciated.