SSRS Database Migration – Cannot View Subscriptions

Moving the SSRS database to another server is a fairly straightforward process. You can detach/attach or Backup (copy_only)/Restore or even generate scripts (really?). The only other thing to do is point reporting services to the new report server database using Reporting Services Configuration manager and you are all set.

Unless of course you also have subscriptions set up in your ReportServer.

If you do, then although the Agent Jobs will run and look like they succeed, no subscriptions are run. Even worse, when you try to view the subscriptions in Report Server you’ll likely receive the following error:

  • You can run the reports manually so there’s no timeout or connection error.
  • You’ve checked the disks, they are also fine.
  • You turn on verbose error messaging and this returns no extra information.

What is not made clear in the Microsoft Documentation (although to be fair, it IS mentioned) is that you need to recreate the RSExecRole in the Master and MSDB databases:

You can do this by running the following code:

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

As soon as you do this, you’ll be able to view your subscriptions again AND they will all run as normal.

Further Reading:

Moving the Report Server Database to another computer

Create the RSExecRole