SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Subscriptions and error message "EXECUTE permission denied on object 'xp_sqlagent_notify', database...


Subscriptions and error message "EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'."

Author
Message
Steve Lund
Steve Lund
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 938
Hi,

I'm trying to setup a subscription on a SSRS report and get the following error message:

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've followed the advice given in this post but still get the same result.

The SQLAgent service is running under a domain account and the Reporting Services service running under a local account. Both these accounts are assigned to the RSExec Role in the master, msdb, ReportServer and ReportServerTempDB databases. This role has execute permissions set as per the above link.

I'm now at a loss as to what else the problem could be?

All ideas welcomed!

Steve.
joshua.clausen
joshua.clausen
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 140
Sorry to necro an old post, but in the interest of improving internet search results for this particular problem, here's what I did to fix the OP's problem (I had the same thing):

There are a number of places I found with the information in the following link:
http://phew-meme.blogspot.com/2007/10/reporting-service-on-report.html


In my specific case, the RsExecRole role in both the "master" and "msdb" databases had had both the "NT Authority\Network Service" and "NTAuthority\System" logins removed from its members (the RSExecRole had had all its members removed by someone doing some maintenance). I compared another Reporting Services installation on another server after reading information in the above link.

Steps:
1. In Mangement Studio connect to the SQL Server instance on which Reporting Services runs.
2. Databases > system Databases
3. master > Security > Roles > RSExecRole (then add whatever logins are required... check in the ReportServer or ReportServerTempDB databases if you're not sure)
4. msdb > Security > Roles > RSExecRole (add the same logins you did to the master DB)

In the end you need the same logins in RSExecRole role for the master, msdb, ReportServer, and ReportServerTempDB databases. ("ReportServer" is the default Reporting Services database name, in case that's not clear Wink
Adam Wilbur
Adam Wilbur
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 591
joshua,

Thanks for the post, you saved me from short term insanity. Crazy



A.J.
DBA with an attitude
Yadava Shettigar
Yadava Shettigar
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 135
Thanks
knightbm
knightbm
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 14
Are you required to restart SQL Server reporting Services??
Carsten Eiberg
Carsten Eiberg
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 116
In my case the report server account already had RSExecRole in all relevant databases. Grant exec on procdure to role in master database helped:

use [master]
GO
GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]
GO


maycol_a15
maycol_a15
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 4
Thank you!! It worked great for me Smile

One more question, is it possible to send an email from a subscription? I cant see the option from the dropdown list...

Thank you!!
Maycol
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2736 Visits: 3499
This worked great for me, too. Thanks!



ptrubs 9865
ptrubs 9865
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 64
I had this issue due to having the Reporting Services databases in an Availability Group. When we failed over (for the first time) there were a bunch of permissions not correctly set, specifically the RSExecRole was not there. The reports worked, but not the subscriptions and we couldn't edit any of the report settings.

Creating the RSExecRole,and assigning specific permissions via this link fixed the problem
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search