Configure ASP.NET Application services to use a schema other than 'dbo'

Question

I want to use ASP.NET Application Services but configure it to use a different schema. I'm using the aspnet_regsql.exe tool as outlined in this URL which by default creates tables and stored procs with a dbo schema.

So i thought i could just generate a database script from existing Application Objects and then find and replace [dbo]'s with [example], and then delete the original Application Services objects and execute the edited script to get the outcome I wanted. Would have only taken a jiffy.

But in reading this Scott Gu blog post there are people in the comments section who have tried this very approach and found it fell over. Scott replied by requesting an email about the issue, which I assume is an acknowledgement of the problem, but I dont see any resolution.

So I'm at the data modelling stage and I really dont want a whole lot of hassle down the track. Does anyone know if this has been resolved or if there continue to be any snags in the find and replace approach to the 'dbo' schema problem?

UPDATE:

So i didn't even get "very far down the track" before the above fell over. It's obviously easy enough to configure the database so that all the objects properly reference each other using the new schema but its appears all the support code has hardwired "dbo" references in it... rather than using the default schema of the connecting user account.

Simply trying to use the VS Studio > Project | ASP.NET CONFIGURATION tool is enough to break it. After altering the web config to point at the new database with the adjusted schemas and then going to the Security Tab of the config tools I get an error "Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'". So it appears 'dbo' has been baked in. :(

UPDATE 2:

So I've added in SYNONYMS as outlined in the accepted answer and it works but I did have to enter 4 records into the database in order to get it rolling. That took a whole 30 seconds. ;)

Before doing this I got the following error:

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'

So I had to copy an application row from the aspnet_Applications table. And then 3 rows from the aspnet_SchemaVersions table. I got these from a default dbo schema install.

This was the aspnet_Applications record (masked 'xxxx' for uniqueness - you'll have to make your own key):

/   /  xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx  NULL

These were the aspnet_SchemaVersions records:

common  1   True

membership  1   True

role manager    1   True

It all works now though. I will update this post if I encounter any problems in the future.

Answer 1

In that case the answer most likely lies with the use of Sql Server SYNONYMS which are like aliases. This article How and why should I use SQL Server 2005 synonyms? has an introduction to them.

You basically want to:

CREATE SYNONYM   
     [dbo].[aspnet_CheckSchemaVersion]
FOR  
     [yourschema].[aspnet_CheckSchemaVersion]

For each affected object. This could be time consuming so this script will likely be of some benefit:

SELECT 'CREATE SYNONYM [dbo].[' + p.Name + '] FOR [' + s.Name + '].[' + p.Name + ']' FROM sys.Procedures p INNER JOIN 
sys.Schemas s on p.schema_id = s.schema_id

You need to copy paste the output of the above script into a new script window and execute that. So the idea is that you're using a script to generate other script commands.

If you then execute the output of the above script, it will then create synonyms for all procedures in the db.

So note:

1. It assumes you have a db with only Application Services procs in it. If you have your own custom procs in there then you will have to add a WHERE conditional to filter for only Application Services objects which start with 'aspnet_' i.e.

   WHERE p.name LIKE 'aspnet_%'.

2. This procedure assumes you've already done a find and replace to setup your new schema. i.e. generated a database script from the original db, done a find and replace on the dbo schema references, then delete/drop original dbo objects, and then run the amended script to get the [schema].[object] setup you want, and then finally use the scripts above.

3. The performance cost of using SYNONYMs will be application dependant.

Sounds like a lot of trouble but the whole procedure will probably take you 3-4 mins.

Answer 2

The output of the script above would be as follows so you can just copy paste this and run a find and replace on it for 'yourschema' to get your SYNONYM build script:

CREATE SYNONYM [dbo].[aspnet_UnRegisterSchemaVersion] FOR [yourschema].[aspnet_UnRegisterSchemaVersion]
CREATE SYNONYM [dbo].[aspnet_RegisterSchemaVersion] FOR [yourschema].[aspnet_RegisterSchemaVersion]
CREATE SYNONYM [dbo].[aspnet_CheckSchemaVersion] FOR [yourschema].[aspnet_CheckSchemaVersion]
CREATE SYNONYM [dbo].[aspnet_Membership_CreateUser] FOR [yourschema].[aspnet_Membership_CreateUser]
CREATE SYNONYM [dbo].[aspnet_Profile_SetProperties] FOR [yourschema].[aspnet_Profile_SetProperties]
CREATE SYNONYM [dbo].[aspnet_Roles_CreateRole] FOR [yourschema].[aspnet_Roles_CreateRole]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings] FOR [yourschema].[aspnet_PersonalizationAllUsers_ResetPageSettings]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings] FOR [yourschema].[aspnet_PersonalizationAllUsers_SetPageSettings]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings] FOR [yourschema].[aspnet_PersonalizationAllUsers_GetPageSettings]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAdministration_GetCountOfState] FOR [yourschema].[aspnet_PersonalizationAdministration_GetCountOfState]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAdministration_ResetSharedState] FOR [yourschema].[aspnet_PersonalizationAdministration_ResetSharedState]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAdministration_ResetUserState] FOR [yourschema].[aspnet_PersonalizationAdministration_ResetUserState]
CREATE SYNONYM [dbo].[aspnet_PersonalizationPerUser_GetPageSettings] FOR [yourschema].[aspnet_PersonalizationPerUser_GetPageSettings]
CREATE SYNONYM [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings] FOR [yourschema].[aspnet_PersonalizationPerUser_ResetPageSettings]
CREATE SYNONYM [dbo].[aspnet_PersonalizationPerUser_SetPageSettings] FOR [yourschema].[aspnet_PersonalizationPerUser_SetPageSettings]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAdministration_DeleteAllState] FOR [yourschema].[aspnet_PersonalizationAdministration_DeleteAllState]
CREATE SYNONYM [dbo].[aspnet_PersonalizationAdministration_FindState] FOR [yourschema].[aspnet_PersonalizationAdministration_FindState]
CREATE SYNONYM [dbo].[aspnet_Profile_DeleteProfiles] FOR [yourschema].[aspnet_Profile_DeleteProfiles]
CREATE SYNONYM [dbo].[aspnet_Setup_RestorePermissions] FOR [yourschema].[aspnet_Setup_RestorePermissions]
CREATE SYNONYM [dbo].[aspnet_Setup_RemoveAllRoleMembers] FOR [yourschema].[aspnet_Setup_RemoveAllRoleMembers]
CREATE SYNONYM [dbo].[aspnet_Membership_FindUsersByName] FOR [yourschema].[aspnet_Membership_FindUsersByName]
CREATE SYNONYM [dbo].[aspnet_Membership_FindUsersByEmail] FOR [yourschema].[aspnet_Membership_FindUsersByEmail]
CREATE SYNONYM [dbo].[aspnet_AnyDataInTables] FOR [yourschema].[aspnet_AnyDataInTables]
CREATE SYNONYM [dbo].[aspnet_Applications_CreateApplication] FOR [yourschema].[aspnet_Applications_CreateApplication]
CREATE SYNONYM [dbo].[aspnet_Users_DeleteUser] FOR [yourschema].[aspnet_Users_DeleteUser]
CREATE SYNONYM [dbo].[aspnet_Users_CreateUser] FOR [yourschema].[aspnet_Users_CreateUser]
CREATE SYNONYM [dbo].[aspnet_Membership_GetUserByName] FOR [yourschema].[aspnet_Membership_GetUserByName]
CREATE SYNONYM [dbo].[aspnet_Membership_GetUserByUserId] FOR [yourschema].[aspnet_Membership_GetUserByUserId]
CREATE SYNONYM [dbo].[aspnet_Membership_GetUserByEmail] FOR [yourschema].[aspnet_Membership_GetUserByEmail]
CREATE SYNONYM [dbo].[aspnet_Membership_GetPasswordWithFormat] FOR [yourschema].[aspnet_Membership_GetPasswordWithFormat]
CREATE SYNONYM [dbo].[aspnet_Membership_UpdateUserInfo] FOR [yourschema].[aspnet_Membership_UpdateUserInfo]
CREATE SYNONYM [dbo].[aspnet_Membership_GetPassword] FOR [yourschema].[aspnet_Membership_GetPassword]
CREATE SYNONYM [dbo].[aspnet_Membership_SetPassword] FOR [yourschema].[aspnet_Membership_SetPassword]
CREATE SYNONYM [dbo].[aspnet_Membership_ResetPassword] FOR [yourschema].[aspnet_Membership_ResetPassword]
CREATE SYNONYM [dbo].[aspnet_Membership_UnlockUser] FOR [yourschema].[aspnet_Membership_UnlockUser]
CREATE SYNONYM [dbo].[aspnet_Membership_UpdateUser] FOR [yourschema].[aspnet_Membership_UpdateUser]
CREATE SYNONYM [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer] FOR [yourschema].[aspnet_Membership_ChangePasswordQuestionAndAnswer]
CREATE SYNONYM [dbo].[aspnet_Membership_GetNumberOfUsersOnline] FOR [yourschema].[aspnet_Membership_GetNumberOfUsersOnline]
CREATE SYNONYM [dbo].[aspnet_Profile_DeleteInactiveProfiles] FOR [yourschema].[aspnet_Profile_DeleteInactiveProfiles]
CREATE SYNONYM [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles] FOR [yourschema].[aspnet_Profile_GetNumberOfInactiveProfiles]
CREATE SYNONYM [dbo].[aspnet_Profile_GetProfiles] FOR [yourschema].[aspnet_Profile_GetProfiles]
CREATE SYNONYM [dbo].[aspnet_UsersInRoles_IsUserInRole] FOR [yourschema].[aspnet_UsersInRoles_IsUserInRole]
CREATE SYNONYM [dbo].[aspnet_UsersInRoles_GetRolesForUser] FOR [yourschema].[aspnet_UsersInRoles_GetRolesForUser]
CREATE SYNONYM [dbo].[aspnet_Roles_DeleteRole] FOR [yourschema].[aspnet_Roles_DeleteRole]
CREATE SYNONYM [dbo].[aspnet_Roles_RoleExists] FOR [yourschema].[aspnet_Roles_RoleExists]
CREATE SYNONYM [dbo].[aspnet_Roles_GetAllRoles] FOR [yourschema].[aspnet_Roles_GetAllRoles]
CREATE SYNONYM [dbo].[aspnet_UsersInRoles_AddUsersToRoles] FOR [yourschema].[aspnet_UsersInRoles_AddUsersToRoles]
CREATE SYNONYM [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] FOR [yourschema].[aspnet_UsersInRoles_RemoveUsersFromRoles]
CREATE SYNONYM [dbo].[aspnet_UsersInRoles_GetUsersInRoles] FOR [yourschema].[aspnet_UsersInRoles_GetUsersInRoles]
CREATE SYNONYM [dbo].[aspnet_UsersInRoles_FindUsersInRole] FOR [yourschema].[aspnet_UsersInRoles_FindUsersInRole]
CREATE SYNONYM [dbo].[aspnet_Profile_GetProperties] FOR [yourschema].[aspnet_Profile_GetProperties]
CREATE SYNONYM [dbo].[aspnet_Paths_CreatePath] FOR [yourschema].[aspnet_Paths_CreatePath]
CREATE SYNONYM [dbo].[aspnet_WebEvent_LogEvent] FOR [yourschema].[aspnet_WebEvent_LogEvent]
CREATE SYNONYM [dbo].[aspnet_Personalization_GetApplicationId] FOR [yourschema].[aspnet_Personalization_GetApplicationId]
CREATE SYNONYM [dbo].[aspnet_Membership_GetAllUsers] FOR [yourschema].[aspnet_Membership_GetAllUsers]