Restore Database Scema on DNN

Restore Database Scema on DNN

CheapASPNETHostingReview.comDotnetnuke standard installs using the dbo as the default user. Some hosts however, like shared hosting service providers, do not allow you to use dbo. So in this case you have to install Dotnetnuke using your assigned username. This should work without any problems. But in case you have an existing website which was installed using the dbo user and you would like to migrate it to a hosting provider that does not allow dbo you have a problem.

DNN-title-banner

The easiest way to migrate a Dotnetnuke website is by backing up the entire web and the database and restore it to the new environment. This will work fine only if your new environment allows dbo. If it does not you will find that when restoring the database all objects will have been assigned to your username and not to dbo and your web will not run.

To resolve this issue you can follow the steps outlined below after which your web will run without any problems. As this procedure requires changes to the databse please be sure to have a backup of your database and website. Note: This procedure is for a SQL server 2005 database!

Step 1:
Open your web.config file and look for “owner”. Change the owner from dbo to your database username. 
Open your web.config file and look for “owner”. Change the owner from dbo to your database username.

Step 2:
In Microsoft SQL Server management studio select all stored procedures that DO NOT have aspnet in their name, rightclick and select “script as create to new query editor window”. 
In Microsoft SQL Server management studio select all stored procedures that DO NOT have aspnet in their name, rightclick and select “script as create to new query editor window”.

This will create a script for all the selected stored procedures.

Step 3:
Where it says “new owner” below you should change it for your database username! 
Where it says “new owner” below you should change it for your database username!

  • In the just created script search and replace “create procedure” with “alter procedure”
  • Search and replace “create procedure” with “alter procedure” (Note! with 2 spaces!)
  • Search and replace “[dbo]” with “[new owner]”
  • Search and replace “dbo.” with “new owner.”
  • Execute the script

Step 4:
In Microsoft SQL Server management studio select all stored views that DO NOT have aspnet in their name, rightclick and select “script as create to new query editor window”. 
In Microsoft SQL Server management studio select all stored views that DO NOT have aspnet in their name, rightclick and select “script as create to new query editor window”.

Step 5:

  • Search and replace “[dbo]” with “[new owner]”
  • Search and replace “dbo.” with “new owner.”
  • Search and replace “create view” with “alter view”
  • Search and replace “new owner.GetListParentKey” width “dbo.GetListParentKey”
  • Execute the script

Step 6:

  • Go to “functions” “Scalar-valued functions”
  • rightclick and select “script as create to new query editor window” for function “fn_GetVersion”
  • Search and replace “dbo.” with “new owner.” (including the “.”!)
  • Execute script

Step 7:

Open a new query window and paste the following script:

Search and replace [USERNAME] for your database username and execute the script.

Step 8:
Refresh your database manager window and you should now see that all AspNet tables and storedprocedures belong to the dbo user/schema. Open your website in your browser. Your website should now load as normal.