Post reply

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

  • February 7, 2008 at 5:40 am

    #203154

    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.

  • December 21, 2008 at 1:35 pm

    #915896

    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 😉

  • May 13, 2009 at 1:12 pm

    #994018

    joshua,

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



    A.J.
    DBA with an attitude

  • September 28, 2009 at 11:57 pm

    #1059317

    Thanks

  • October 9, 2013 at 3:32 pm

    #1657330

    Are you required to restart SQL Server reporting Services??

  • April 27, 2015 at 6:11 am

    #1793622

    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

  • June 11, 2015 at 9:53 am

    #1804758

    Thank you!! It worked great for me 🙂

    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

  • November 18, 2015 at 9:54 am

    #1840768

    This worked great for me, too. Thanks!

  • March 26, 2017 at 8:57 pm

    #1935005

    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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply