CheapASPNETHostingReview.com | Dotnetnuke 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.
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Applications ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Profile ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Roles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_SchemaVersions ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Users ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UsersInRoles ALTER SCHEMA dbo TRANSFER [USERNAME].vw_aspnet_Applications ALTER SCHEMA dbo TRANSFER [USERNAME].vw_aspnet_MembershipUsers ALTER SCHEMA dbo TRANSFER [USERNAME].vw_aspnet_Profiles ALTER SCHEMA dbo TRANSFER [USERNAME].vw_aspnet_Roles ALTER SCHEMA dbo TRANSFER [USERNAME].vw_aspnet_Users ALTER SCHEMA dbo TRANSFER [USERNAME].vw_aspnet_UsersInRoles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Applications_CreateApplication ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UnRegisterSchemaVersion ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_CheckSchemaVersion ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Users_CreateUser ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Users_DeleteUser ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_AnyDataInTables ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_CreateUser ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_GetUserByName ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_GetUserByUserId ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_GetUserByEmail ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_GetPasswordWithFormat ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_UpdateUserInfo ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_GetPassword ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_SetPassword ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_ResetPassword ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_UnlockUser ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_UpdateUser ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_ChangePasswordQuestionAndAnswer ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_GetAllUsers ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_GetNumberOfUsersOnline ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_FindUsersByName ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Membership_FindUsersByEmail ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Profile_GetProperties ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Profile_SetProperties ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Profile_DeleteProfiles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Profile_DeleteInactiveProfiles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Profile_GetNumberOfInactiveProfiles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Profile_GetProfiles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UsersInRoles_IsUserInRole ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UsersInRoles_GetRolesForUser ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Roles_CreateRole ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Roles_DeleteRole ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Roles_RoleExists ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UsersInRoles_AddUsersToRoles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UsersInRoles_RemoveUsersFromRolesALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UsersInRoles_GetUsersInRoles ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_UsersInRoles_FindUsersInRole ALTER SCHEMA dbo TRANSFER [USERNAME].aspnet_Roles_GetAllRoles Grant execute on aspnet_Setup_RestorePermissions to [USERNAME] Grant execute on aspnet_Setup_RemoveAllRoleMembers to [USERNAME] Grant execute on aspnet_RegisterSchemaVersion to [USERNAME] Grant execute on aspnet_Applications_CreateApplication to [USERNAME] Grant execute on aspnet_UnRegisterSchemaVersion to [USERNAME] Grant execute on aspnet_Users_CreateUser to [USERNAME] Grant execute on aspnet_Users_DeleteUser to [USERNAME] Grant execute on aspnet_AnyDataInTables to [USERNAME] Grant execute on aspnet_Membership_CreateUser to [USERNAME] Grant execute on aspnet_Membership_GetUserByName to [USERNAME] Grant execute on aspnet_Membership_GetUserByUserId to [USERNAME] Grant execute on aspnet_Membership_GetUserByEmail to [USERNAME] Grant execute on aspnet_Membership_GetPasswordWithFormat to [USERNAME] Grant execute on aspnet_Membership_UpdateUserInfo to [USERNAME] Grant execute on aspnet_Membership_GetPassword to [USERNAME] Grant execute on aspnet_Membership_SetPassword to [USERNAME] Grant execute on aspnet_Membership_ResetPassword to [USERNAME] Grant execute on aspnet_Membership_UnlockUser to [USERNAME] Grant execute on aspnet_Membership_UpdateUser to [USERNAME] Grant execute on aspnet_Membership_ChangePasswordQuestionAndAnswer to [USERNAME] Grant execute on aspnet_Membership_GetAllUsers to [USERNAME] Grant execute on aspnet_Membership_GetNumberOfUsersOnline to [USERNAME] Grant execute on aspnet_Membership_FindUsersByName to [USERNAME] Grant execute on aspnet_Membership_FindUsersByEmail to [USERNAME] Grant execute on aspnet_Profile_GetProperties to [USERNAME] Grant execute on aspnet_Profile_SetProperties to [USERNAME] Grant execute on aspnet_Profile_DeleteProfiles to [USERNAME] Grant execute on aspnet_Profile_DeleteInactiveProfiles to [USERNAME] Grant execute on aspnet_Profile_GetNumberOfInactiveProfiles to [USERNAME] Grant execute on aspnet_Profile_GetProfiles to [USERNAME] Grant execute on aspnet_UsersInRoles_IsUserInRole to [USERNAME] Grant execute on aspnet_UsersInRoles_GetRolesForUser to [USERNAME] Grant execute on aspnet_Roles_CreateRole to [USERNAME] Grant execute on aspnet_Roles_DeleteRole to [USERNAME] Grant execute on aspnet_Roles_RoleExists to [USERNAME] Grant execute on aspnet_UsersInRoles_AddUsersToRoles to [USERNAME] Grant execute on aspnet_UsersInRoles_RemoveUsersFromRoles to [USERNAME] Grant execute on aspnet_UsersInRoles_GetUsersInRoles to [USERNAME] Grant execute on aspnet_UsersInRoles_FindUsersInRole to [USERNAME] Grant execute on aspnet_Roles_GetAllRoles to [USERNAME] Grant execute on aspnet_CheckSchemaVersion to [USERNAME] Grant execute on GetListParentKey to [USERNAME] |
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.