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

Sybilsybila

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.

Determiner

Excellent solution to remove the membership default table dependencies. Thanks! – Multitude

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.

Delacroix

Answer 3

Try this:

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

Chicane

This worked for me. It's a clever hack. However, the autoCreateTables must be set to false if you already have created the tables, otherwise it tries to create the already existing table. – Onomasiology

Answer 4

I think the key is that it will use whatever the default schema is associated with your user id. In SQL Server, you might try specifying a default schema other than dbo for your user account and then I think it will create the tables in that schema, but I don't think you can specify the schema on the call to InitializeDatabaseConnection.

Rainy