While doing an MDS upgrade from SQL 2012 to 2019, when we do a database upgrade on MDS Configuration tool the upgrade fails with the error:
Microsoft.MasterDataServices.Configuration.ConfigurationException: Cannot find the object 'viw_SYSTEM_SECURITY_ROLE_ACCESSCONTROL_FUNCTIONAL', because it does not exist or you do not have permission. ---> System.Data.SqlClient.SqlException: Cannot find the object 'viw_SYSTEM_SECURITY_ROLE_ACCESSCONTROL_FUNCTIONAL', because it does not exist or you do not have permission.
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.MasterDataServices.Configuration.DatabaseUtilities.ProcessSqlStringWithTransaction(String commandSql, SqlConnection connection, SqlTransaction upgradeTransaction)
at Microsoft.MasterDataServices.Configuration.DatabaseUtilities.UpgradeMasterDataServicesDatabase(SqlConnection connection, String databaseName, SkuTypes skuType, Collection`1& businessRuleExclusionWarnings)
at Microsoft.MasterDataServices.Configuration.Commands.UpdateMasterDataServicesDatabase.InternalProcess()
at Microsoft.MasterDataServices.Configuration.Commands.SqlCmd`1.Execute()
at Microsoft.MasterDataServices.Configuration.InstanceManager.UpgradeMasterDataServicesDatabase(DatabaseServerInformation serverInformation, DatabaseInformation databaseInformation)
--- End of inner exception stack trace ---
at Microsoft.MasterDataServices.Configuration.InstanceManager.UpgradeMasterDataServicesDatabase(DatabaseServerInformation serverInformation, DatabaseInformation databaseInformation)
at Microsoft.MasterDataServices.Configuration.ConfigurationAdministration.UpgradeDatabase(DatabaseInformation databaseInformation)
at Microsoft.MasterDataServices.Configuration.UI.MdsDatabase.UpgradeDatabase()
The view mentioned does not exist in the existing old version of MDS database and when we try to upgrade it gives the error. Just to check I did try creating a new database instead of upgrading the database and the view does get created. I am new to MDS so any inputs or pointers will be very helpful.