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

    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

    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

    Any help is greatly appreciated.

    Thanks


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

    Thursday, December 19, 2013 7:50 PM

Answers

  • 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.

    Regards,
    Heidi Duan

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


    Heidi Duan
    TechNet Community Support


    Monday, December 23, 2013 9:53 AM
    Moderator