Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

An Error occurred try again later when trying to modify subscription

I am attempting to do an upgrade on Reporting Services 2012 to upgrade it to PowerBI Reporting Services 2017.   When I attempt to edit a subscription, I get an error message that says oops, an erro...
  • Anonymous's avatar
    Anonymous
    7 years ago

    I found the answer after reading a myriad of articles.

     

    The problem for me is that the RSExecRole was created without any grants added to it.

     

    So, I found this very helpful article that tells you how the RSExecRole should be setup:

     

    https://docs.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-2017

     

    The bottom line is that you need to run this script.

     

    USE master;
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
    CREATE ROLE [RSExecRole];
    END
    GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO [RSExecRole];
    GRANT EXECUTE ON dbo.xp_sqlagent_is_starting TO [RSExecRole];
    GRANT EXECUTE ON dbo.xp_sqlagent_notify TO [RSExecRole];
    GO
    USE msdb;
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
    CREATE ROLE [RSExecRole];
    END
    GRANT EXECUTE ON dbo.sp_add_category TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_add_job TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_add_jobschedule TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_add_jobserver TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_add_jobstep TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_delete_job TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_help_category TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_help_job TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_help_jobschedule TO [RSExecRole];
    GRANT EXECUTE ON dbo.sp_verify_job_identifiers TO [RSExecRole];
    GRANT SELECT ON dbo.syscategories TO [RSExecRole];
    GRANT SELECT ON dbo.sysjobs TO [RSExecRole];
    GO

     

    After that I could edit subscriptions and users could create and edit them.

     

    Hope this helps someone.