WebSecurity.InitializeDatabaseConnection - How specify a db schema?

Question

I am using SimpleMembership (http://blog.osbornm.com/archive/2010/07/21/using-simplemembership-with-asp.net-webpages.aspx) but I am unable to have place my custom user table in a schema other than dbo.

For example, this call will stubbornly create a table named "dbo.MySchema.User"

WebSecurity.InitializeDatabaseConnection(connectionStringName: "ApplicationServices", userTableName: "MySchema.User", userIdColumn: "ID", userNameColumn: "Username", autoCreateTables: true);

I also tried creating the table manually but the library still tries to append "dbo" when running the query.

Do I need a custom provider? I'm not 100% sure if I will use SimpleMembership however it could save me the trouble of writing a bunch of user/auth code.

http://msdn.microsoft.com/en-us/library/gg569134(v=VS.99).aspx

Answer 1

I use my own schema for users.

My table is called CORE.PROFILES, to work with websecurity I have a SQL synonym dbo.PROFILES.

To do it run this statement in SQL Server:

CREATE SYNONYM dbo.profiles FOR core.profiles;

I hope this help you.

Answer 2

Version 2.0.0.0 of the WebMatrix.WebData assembly generates its create table statement as follows.

"CREATE TABLE " + this.SafeUserTableName + "(" + this.SafeUserIdColumn + " int NOT NULL PRIMARY KEY IDENTITY, " + this.SafeUserNameColumn + " nvarchar(56) NOT NULL UNIQUE)"

SafeUserTableName has the following implementation.

return "[" + this.UserTableName + "]";

As @anderly assumed, the result will be that the table will be created in the default schema of the user in the connection string.

By the same token, at runtime, the implementation of the SafeUserTableName method results in the entire contents of the UserTableName parameter being interpreted as a table name - in the default schema.

SimpleMembershipProvider therefore requires the user table to be in the default schema.