Transfer schema ownership to dbo for an umbraco database

How I resolved a problem with schema ownership when moving an Umbraco database from the shared-hosting stage server to the live database server.


I've just setup a new Umbraco site and had to backup and restore the Umbraco database from the staging to the live database server.

 

This didn't go well, as the schema ownership for all tables were set to the schema used on the stage database and weren't accessible on the live server.

 

I used the following SQL script to change the schema for the Umbraco tables to dbo.

SQL

Just change old-schema to the schema you want to transfer ownership from.

ALTER SCHEMA dbo TRANSFER old-schema.cmsContent;
ALTER SCHEMA dbo TRANSFER old-schema.cmsContentType; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsContentType2ContentType;
ALTER SCHEMA dbo TRANSFER old-schema.cmsContentTypeAllowedContentType; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsContentVersion; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsContentXml; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsDataType; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsDataTypePreValues; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsDictionary; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsDocument; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsDocumentType; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsLanguageText; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsMacro; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsMacroProperty; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsMember; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsMember2MemberGroup; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsMemberType; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsPreviewXml; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsPropertyData; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsPropertyType; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsPropertyTypeGroup; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsStylesheet; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsStylesheetProperty; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsTagRelationship; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsTags; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsTask; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsTaskType; 
ALTER SCHEMA dbo TRANSFER old-schema.cmsTemplate; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoAccess; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoAccessRule; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoCacheInstruction; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoDeployChecksum; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoDeployDependency; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoDomains; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoExternalLogin; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoLanguage; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoLog; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoMigration; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoNode; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoRelation; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoRelationType; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoServer; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoUser; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoUser2app; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoUser2NodeNotify; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoUser2NodePermission; 
ALTER SCHEMA dbo TRANSFER old-schema.umbracoUserType;

Comments

Pete's Code Library

Peter Edney at a wedding

My code library is where I keep all my useful bits of code that I refer to over and again. They are generally incomplete and are a quick tool to remind me of how to resolve an issue.

Categories