Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

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

Dave Mason - SSRS

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:

Dave Mason - Report Server Configuration Manager

Dave Mason - SSRS Change Database

Dave Mason - SSRS Change Database

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)
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')
sp_addrole 'RSExecRole'

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

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

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

(0 rows affected)

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

Dave Mason - SQL Server Schemas

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!

Dave Mason - Report Server Configuration Manager


Post comment