Error thrown on Managing caching option of a dataset on SSRS 2012 integrated mode report on Sharepoint 2010 SP1 RRS feed

  • Question

  • The error I am facing is

    Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.   

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   

    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   

    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)   

    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)   

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)   

    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)   

    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()   

    at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()     -

    -- End of inner exception stack trace ---   

    at Microsoft.ReportingServices.Library.Storage.WrapAndThrowKnownExceptionTypes(Exception e)   

    at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()   

    at Microsoft.ReportingServices.Library.SqlAgentScheduler.get_IsSchedulerRunning()   

    at Microsoft.ReportingServices.Library.SchedulingDBInterface.CheckIfSchedulerIsRunning(Boolean error)   

    at Microsoft.ReportingServices.Library.SchedulingDBInterface.ListTasks(CatalogItemPath path)   

    at Microsoft.ReportingServices.Library.ScheduleCoordinator.ListTasksAsArray(ExternalItemPath externalPath)   

    at Microsoft.ReportingServices.Library.ListSchedulesAction.PerformActionNow()   

    at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()

    I ran the following script, but it did not resolve my error

    USE master


    GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole


    GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole


    GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole


    USE msdb


    -- Permissions for SQL Agent SP's

    GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole


    GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole


    GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole


    GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole


    Any help is greatly appreciated.


    An ounce of practice is worth more than tons of preaching.

    Thursday, December 19, 2013 7:50 PM


  • Hi SivaSP,

    Sorry for the delay.

    Based on the error log, the issue may occur because the RSExecRole in master and msdb databases don’t have necessary securable with appropriate privilege in order to contact the SQL Agent to run subscriptions.

    In your scenario, you can try to follow the steps below:

    1. Open SharePoint 201 Central Administration
    2. Application Management
    3. Manage Service Applications
    4. Click on your SQL Server Reporting Services Service Application
    5. Click on “Provision Subscriptions and Alerts” link
    6. Type a username and password for SQL Server which grants privilege to your SSRS service application pool.

    Hope this helps.

    Heidi Duan

    If you have any feedback on our support, please click here.

    Heidi Duan
    TechNet Community Support

    • Edited by Heidi-Duan Monday, December 23, 2013 9:53 AM edit
    • Marked as answer by Heidi-Duan Friday, December 27, 2013 5:41 AM
    Monday, December 23, 2013 9:53 AM