There is already an object named 'RSExecRole' in the database

2019-11-03

When migrating an instance of SSRS, I performed a backup of the [ReportServer] and [ReportServerTemp] SSRS databases from a SQL Server 2008 R2 instance and restored them to a SQL Server 2017 instance. After installing SSRS 2017 on the target machine, I ran SSRS configuration and attempted to set the Current Report Server Database to the existing [ReportServer] database I had already restored:

At the last step (Progress and Finish), here is the text of the error message:


System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'RSExecRole' in the database.
CREATE SCHEMA failed due to previous errors.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
   at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
ClientConnectionId:4f47a341-9eca-4963-bfcd-ba707d8882a0
Error Number:2714,State:6,Class:16

I was somewhat familiar with the RSExecRole database role. Knowing it had to exist for SSRS to function, I was initially puzzled. After running an Extended Events session for the Error_Reported event, I found this SQL statement that was failing in the context of the [master] database:


if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')  
BEGIN   
 EXEC sp_addrole 'RSExecRole'  
END

That role definitely did not exist in [master]. In fact, there was not an "object" in [master] by that name:


SELECT o.name, o.type_desc
FROM master.sys.objects o
WHERE o.name = 'RSExecRole'

name        type_desc
----------  -----------------

(0 rows affected)

Upon further inspection, I realized it was an existing schema named RSExecRole that was causing the issue.

If I had paid just a wee bit more attention to the error message I would have discovered this sooner:


CREATE SCHEMA failed due to previous errors.

For a quick turnaround, I deleted the RSExecRole schema from [master] and also from [msdb] (the Extended Events session tipped me off to this), re-ran "Change Database" in SSRS Configuration and finally got the results I was expecting. Happy migrating, everyone!

Comments