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
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
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
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!
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.
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.
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
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
August 26th, 2008 9:05am
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
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
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.
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
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
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/
March 19th, 2015 2:02am