Post Pic

Invalid object name ‘umbracoUser’

Umbraco logoYet another problem on an Umbraco v4.0 site I am working on currently. The site will be hosted on a VPS with Plesk as mentioned in an earlier post, I needed to restore a backup of the remote database locally but after restoring it my local development copy throws the exception “Invalid object name ‘umbracoUser”. After an hour or so of frustration and searching for a solution I realised that the table owner and schema belong to a user on the remote data source which didn’t exist locally.

The Solution:

Run the following SQL statement and output to text and it will generate all of the ALTER statements need to change ownership of the object to DBO(Database Owner):

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.tables p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'EXISTING_OWNER_USERNAME'

What you should find is that you are presented with a list of alter statements similar to those below which if you execute will change owner of all tables owned by the owner you specified in the first step and assign ownership to DBO:

ALTER SCHEMA dbo TRANSFER oldowner.cmsContent
ALTER SCHEMA dbo TRANSFER oldowner.cmsContentType
ALTER SCHEMA dbo TRANSFER oldowner.cmsContentTypeAllowedContentType
ALTER SCHEMA dbo TRANSFER oldowner.cmsContentVersion
ALTER SCHEMA dbo TRANSFER oldowner.cmsContentXml
ALTER SCHEMA dbo TRANSFER oldowner.cmsDataType
ALTER SCHEMA dbo TRANSFER oldowner.cmsDataTypePreValues
ALTER SCHEMA dbo TRANSFER oldowner.cmsDictionary
ALTER SCHEMA dbo TRANSFER oldowner.cmsDocument
ALTER SCHEMA dbo TRANSFER oldowner.cmsDocumentType
ALTER SCHEMA dbo TRANSFER oldowner.cmsLanguageText
ALTER SCHEMA dbo TRANSFER oldowner.cmsMacro
ALTER SCHEMA dbo TRANSFER oldowner.cmsMacroProperty
ALTER SCHEMA dbo TRANSFER oldowner.cmsMacroPropertyType
ALTER SCHEMA dbo TRANSFER oldowner.cmsMember
ALTER SCHEMA dbo TRANSFER oldowner.cmsMember2MemberGroup
ALTER SCHEMA dbo TRANSFER oldowner.cmsMemberType
ALTER SCHEMA dbo TRANSFER oldowner.cmsPropertyData
ALTER SCHEMA dbo TRANSFER oldowner.cmsPropertyType
ALTER SCHEMA dbo TRANSFER oldowner.cmsStylesheet
ALTER SCHEMA dbo TRANSFER oldowner.cmsStylesheetProperty
ALTER SCHEMA dbo TRANSFER oldowner.cmsTab
ALTER SCHEMA dbo TRANSFER oldowner.cmsTagRelationship
ALTER SCHEMA dbo TRANSFER oldowner.cmsTags
ALTER SCHEMA dbo TRANSFER oldowner.cmsTemplate
ALTER SCHEMA dbo TRANSFER oldowner.umbracoApp
ALTER SCHEMA dbo TRANSFER oldowner.umbracoAppTree
ALTER SCHEMA dbo TRANSFER oldowner.umbracoDomains
ALTER SCHEMA dbo TRANSFER oldowner.umbracoLanguage
ALTER SCHEMA dbo TRANSFER oldowner.umbracoLog
ALTER SCHEMA dbo TRANSFER oldowner.umbracoNode
ALTER SCHEMA dbo TRANSFER oldowner.umbracoRelation
ALTER SCHEMA dbo TRANSFER oldowner.umbracoRelationType
ALTER SCHEMA dbo TRANSFER oldowner.umbracoStatEntry
ALTER SCHEMA dbo TRANSFER oldowner.umbracoStatSession
ALTER SCHEMA dbo TRANSFER oldowner.umbracoStylesheet
ALTER SCHEMA dbo TRANSFER oldowner.umbracoStylesheetProperty
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUser
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUser2app
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUser2NodeNotify
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUser2NodePermission
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUser2userGroup
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUserGroup
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUserLogins
ALTER SCHEMA dbo TRANSFER oldowner.umbracoUserType
ALTER SCHEMA dbo TRANSFER oldowner.cmsContentTypes
ALTER SCHEMA dbo TRANSFER oldowner.umbracoContent
ALTER SCHEMA dbo TRANSFER oldowner.umbracoContentAll

After executing the above everything started working again as expected.

Thanks to a blog post by Steve Schofield that led me to the solution.

Umbraco Certified Developer
SagePay Approved Partner
Creative Market