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?
June 10th, 2005 1:44pm

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

Free Windows Admin Tool Kit Click here and download it now
June 10th, 2005 4:41pm

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?
June 10th, 2005 5:10pm

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
Free Windows Admin Tool Kit Click here and download it now
June 10th, 2005 5:20pm

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
June 10th, 2005 6:24pm

Hi,

wehave 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

Free Windows Admin Tool Kit Click here and download it now
May 16th, 2007 2:41pm

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

John Cutsinger
Altegris
June 13th, 2007 12:55am

This also solved the problem for me. Thanks!

Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2007 9:00am

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

August 27th, 2007 3:59pm

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.

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2007 1:46pm

Have you found out what was the problem? I am facing the same issue even after I have ran the scripts to give rights to RSExecRole.....Very frustrating now!!!!
October 9th, 2007 3:32pm

Hi. No I haven't. I stopped tryig to solve the problem as scheduling is not available on SQL Express, and that is what we have to work with at the moment.

Sorry I can't be of help.

At some point our client might purchase an SQL license and then I will probably look into this again. Perhaps a fresh installation will make all the difference.

Good luck.

Please do post here if you find a solution.

Thank you.

Free Windows Admin Tool Kit Click here and download it now
October 10th, 2007 5:19am

Add NT AUTHORITY\NETWORK SERVICE user to RSExecRole in Master database

October 17th, 2007 5:22pm

HI.

I got my SSRS Subscription works with this:

Code Block

USE [master]

GO

EXEC sp_addrolemember N'RSExecRole', N'Domain\User Service Account' -- SSRS Service Running

GO

USE [msdb]

GO

EXEC sp_addrolemember N'RSExecRole', N'Domain\User Service Account'-- SSRS Service Running

GO

Only is necesary add the user service acount to RSExecRole at MASTER and MSDB databases.

Krisstel Hdez & Gustavo de la Cruz

HBMX SQL Server DBA's

Free Windows Admin Tool Kit Click here and download it now
November 7th, 2007 7:21pm

i am still getting the same error, when i am clicking on New subscription option

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

Any help would be highly appreciated...

Thanks

Tapan

March 14th, 2008 6:26pm

I'm hoping someone can help me. I ran the script and I have made sure the domain account which runs the reporting service is a member of the RSExecRole, both in master and in msdb.

I am still getting an error
The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

Does anyone have a solution to this? Our server has been up and running for over a year now, but it is only now that the users are requesting subscription and thats when we get the error. Everything else seems to work.


Hoping for a quick response

sgeir Gunnarsson


edit: I forgot to say that we have one machine running reporting services and another running the databases. Hope this helps with solving the problem

  • Proposed as answer by Paul is here Tuesday, February 17, 2009 7:42 PM
Free Windows Admin Tool Kit Click here and download it now
August 26th, 2008 9:05am

Nilesh Hirpara wrote:

Add NT AUTHORITY\NETWORK SERVICE user to RSExecRole in Master database

Having the same problem. Adding NT AUTHORITY\NETWORK SERVICE to RSExecRole fix my problem.

Thanks!

September 2nd, 2008 11:33pm

Go to SSMS Server -> Security -> Logins; select Properties of the login you're using for ReportServer to connect to the RS databases.
Then check checkboxes next to 'master' and 'msdb' databases. Those databases have to be mapped to that user in order to work.
  • Proposed as answer by Paul is here Tuesday, February 17, 2009 7:42 PM
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2009 7:41pm

I just cannot understand this. I have the RSExecRole in all four databases. It has the required permission on the stored procedures and both network service which is the web service account and the domain account which is the windows service account are in the role on master and msdb. But I still get the the same error The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

The network service account has public role and sys as a default schema on master and msdb.

We have to different servers, one is running ssrs and one is a database server. The network service is the account running the web service on the ssrs server and the domain account runs the windows service on the ssrs server.

Can anyone think of anything more I can do to resolve this issue?

sgeir Gunnarsson

February 27th, 2009 11:18am

I just cannot understand this. I have the RSExecRole in all four databases. It has the required permission on the stored procedures and both network service which is the web service account and the domain account which is the windows service account are in the role on master and msdb. But I still get the the same error The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

The network service account has public role and sys as a default schema on master and msdb.

We have to different servers, one is running ssrs and one is a database server. The network service is the account running the web service on the ssrs server and the domain account runs the windows service on the ssrs server.

Can anyone think of anything more I can do to resolve this issue?

sgeir Gunnarsson

try to add SSRS runnig account intoRSExecRoleon bothmaster and msdb
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2009 3:45am

I too am having this issue. The service account is part of the RSExecRole on both master and msdb, as well as the reportserver and reportservertempdb. Same with the Network Service account. The RSExecRole has all the required stored procedures and execute/select permissions that are stated. These accounts (obviously if they're part of the roles) have access to the databases. I receive this same error when attempting to create a subscription, when going to history, and when going to execution. Very frustrating.
June 25th, 2009 9:41pm

If you use the report configuration tool and select the database tab you can have it generate the various scripts for you to run later.
Free Windows Admin Tool Kit Click here and download it now
July 29th, 2009 3:06pm

Make sure in addition to your SQL Server Service Account, that the Reporting Services Service Acccount is also added to the RSExecRole in master and msdb.  Because in my case the Reporting Services Service Account is different from the SQL Server Service Account.  We have Reporting Services on separate server from the ReportServer db and when you go under Reporting Services configuration you may see the account is different.  Cheers!
  • Proposed as answer by Mahajan Monday, November 22, 2010 4:00 PM
March 22nd, 2010 3:32pm

I checked the user under which Reporting server is running and gave RsExecute role to the user and that is it. it showed all the options to create new subscription.

Thanks for the help

Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2010 4:03pm

Thank you for this post. Solved my problem, which I would never have figured out on my own.
May 25th, 2011 9:50pm

This helped me a lot. Thanks

Makarand

+91 - 98811 53116

Free Windows Admin Tool Kit Click here and download it now
February 29th, 2012 12:14pm

Hi

I had exactly the same issue with a visual studio 2012 installation and TFS on SQL Reporting Services 2008. 

This solution helped me out.

The key seems to be ensuring that the role exists and you grant your Reporting Service account the role. Then ensure the permissions are applied as above.

Many thanks

December 20th, 2012 11:24am

Hi, problem is you did not finished configuration of SSRS by running provisioning script. You can do this as well later on. In Central Administration you can get right script. More details here:

https://joyfulcraftsmen.wordpress.com/2015/03/17/sharepoint-ssrs-subscriptions-xp_sqlagent_notify-error/
Free Windows Admin Tool Kit Click here and download it now
March 19th, 2015 2:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics