none
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

    Question

  • I am getting the following error when I execute an SSRS report.

    The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

    The report is executing a stored proc. Which in turn is running an Agent Job.

    When I run this report through BIDS it works fine. It is only when I run it though Report Manager I get a problem.

    Can anybody help?


    Mr Shaw
    Thursday, November 11, 2010 12:19 PM

Answers

  • There is no need to do that. the SA user is a sysadmin and has full control over the entire database.

    Some simple checks, are you connecting to the same database in BIDS as the report is on the server ie is the datasource pointed to the same server.

    I'm pretty confident that the login the ReportServer is using to connect to the database is different from that of BIDS and does not have the relevant permissions.

    You could run SQL Profiler on the database to see which login is being used from Report Server.


    every day is a school day
    • Marked as answer by Mr Shaw Thursday, November 11, 2010 3:36 PM
    Thursday, November 11, 2010 1:34 PM

All replies

  • What are the connection properties of your data source - using Windows Authentication? Are you using a remote SQL Server i.e. on a different machine to that of the report server?

    It may be that the data source is connecting with the Service Account and that doesn't have the relevant permissions to the msdb database.


    every day is a school day

    Thursday, November 11, 2010 12:27 PM
  • SQL Server 2005.

    Windows Authentication.

    Everything is on the same server.

     


    Mr Shaw
    Thursday, November 11, 2010 12:43 PM
  • The SA is not listed as a user of the the msdb.

    What are the implication of adding SA as a user to the msdb database?


    Mr Shaw
    Thursday, November 11, 2010 12:48 PM
  • There is no need to do that. the SA user is a sysadmin and has full control over the entire database.

    Some simple checks, are you connecting to the same database in BIDS as the report is on the server ie is the datasource pointed to the same server.

    I'm pretty confident that the login the ReportServer is using to connect to the database is different from that of BIDS and does not have the relevant permissions.

    You could run SQL Profiler on the database to see which login is being used from Report Server.


    every day is a school day
    • Marked as answer by Mr Shaw Thursday, November 11, 2010 3:36 PM
    Thursday, November 11, 2010 1:34 PM
  • I ran it under BIDS with the profiler and it showed my account.

    I also ran it through the Report Manager and it showed the Reporting account.


    Mr Shaw
    Thursday, November 11, 2010 2:39 PM
  • And does the Reporting account have the correct permissions ie on sp_start_job? What permissions in the msdb database does the reporting account have?
    every day is a school day
    Thursday, November 11, 2010 3:20 PM
  • None.. it is not listed as a user in the MSDB database
    Mr Shaw
    Thursday, November 11, 2010 3:22 PM
  • Ok i have added the Reporting user and to msdb and it all works fine.

    does this create any secuirty or performance issues?


    Mr Shaw
    Thursday, November 11, 2010 3:36 PM
  • Add the Reporting account as a user in the database and give it EXECUTE AS permissions on the start_job procedure or add it to the SQLAgentOperator fixed database role.
    every day is a school day
    Thursday, November 11, 2010 3:37 PM