SSRS Report Manager Configuration Wizard failed when Applying Connection Rights

  • Article

The SQL Server 2012 SSRS Report Manager Configuration Wizard failed when Changing Database on step "Applying Connection Rights"

Error Message: Cannot alter the role 'RSExecRole', because it does not exist or you do not have permission.

Solution: The login used to connect to the database server did not have sufficient privileges on MSDB and Master. After granting the login permission to execute sp_addrolemember, the wizard completed and the SSRS database server was successfully updated.

Details: Today one of my customers encountered an issue while changing the database for an existing SQL Server 2012 Reporting Server instance as part of a migration. The SSRS instance was originally configured with the ReportServer and ReportServerTempDB database on one server. As part of a migration, they moved the SSRS databases to an instance on another server that already contained other Reporting Services databases with different names. As a result, the migration was not quite the standard migration (for example: the RSExecRole already existing on the machine, but would be used by both sets of DBs). They thought that everything had been set correctly with permissions, but hit the following error on the “Applying Connection Rights” step when attempting to change the database.

 

SSRS Applying Connection Rights

The error message shows:

System.Data.SqlClient.SqlException: Cannot alter the role 'RSExecRole', because it does not exist or you do not have permission.

To troubleshoot the issue, we ran SQL Profiler (with a filter on the specific login to minimize traffic) to capture the statements being executed, then stepped through the wizard again. When we viewed the results in Profiler, we noticed that the wizard was failing on the following step:

EXEC sp_addrolemember 'RSExecRole', @name_in_db

The interesting thing at this point is that the login had already been added to the RSExecRole, so this line was technically not necessary to run, but it is still executed as part of the wizard. Since the login was a member in the role, but DID NOT have permission to execute this stored procedure against MSDB and Master, it failed on this step.

We granted ALTER permission on the role in MSDN and Mater and the installer was able to complete successfully. As mentioned in the TechNet sp_addrolemember documentation, you can also do this by either 1) granting membership to the login in the db_securityadmin or db_owner fixed database role, 2) granting membership to the login in the role that owns the role, or 3) granting ALTER ANY ROLE permission or ALTER permission on the role.

Thanks,
Sam Lester (MSFT)