Yet 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.
Related Posts
Related posts brought to you by Yet Another Related Posts Plugin.
4 Responses
Hey thanks for the tip. You saved me from a lot of wasted time during this Umbraco site transfer.
Thanks for the post. It worked like a charm.
when i will run umbraco project below error occured pls help me
Invalid object name ‘umbracoUser’.
The solution is in the post above. You have most likely had tables created in the context of another user rather than dbo.