ASP.Net MVC 4 Simple Membership specify Schema

Question

I'm trying to figure out how to setup ASP.Net MVC 4's Simple Membership. The tables that house users in my current database have a different schema then 'dbo'. How can I specify the schema when initializing my database connection:

            WebSecurity.InitializeDatabaseConnection
            (
                connectionStringName: "GuessAListConnection",
                userTableName: "UserProfile",
                userIdColumn: "UserId",
                userNameColumn: "UserName",
                autoCreateTables: false
            );

There's not an option for schema. I've tried appending schema to the userTableName but that produces an error. Any suggestions?

Thanks

Tom

1

Have a look on this thread: – von v. Mar 27 '13 at 06:13

score 1 · Answer 1 · answered Mar 27 '13 at 06:50

1

WebSecurity will use whatever the default schema of the database user. No way to specify a different schema that i'm aware of.

answered Mar 27 '13 at 06:50

Erik Funkenbusch

92,674
28
195
291

Tom Schreck · Answer 2 · 2013-03-27T20:44:34.147

I found another solution if you have your user/authentication tables in a different schema than dbo (or whatever the default schema of the database user) and that's to use a view.

In my database, my user/authentication tables are scoped to a "Security" schema. I also have a Person schema where the Person table lives. I store just FirstName, LastName in Person table.

I created a view that pulls all of the tables together for authentication and am returning the following fields:

UserId, UserName, PersonId, FirstName, LastName, AuthenticationType, LastLoginDate, IsLockedOut

In my application, I can authenticate a person a number of different ways: Form, Windows, Facebook, Twitter, etc. The UserId, UserName relate to the different authentication type.

Here's my UserProfile class in ASP.Net MVC:

  [Table("__vwRegisteredUser")]
  public class UserProfile
  {
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public String UserName { get; set; }
    public int PersonId { get; set; }
    public String FirstName { get; set; }
    public String LastName { get; set; }
    public String AuthenticationTypeName { get; set; }
    public Boolean IsLockedOut { get; set; }
    public DateTime LastLoginDate { get; set; }

    public int Id
    {
        get
        {
            return PersonId;
        }
    }

    public String Name
    {
        get
        {
            return String.Format("{0} {1}", FirstName, LastName);
        }
    }
}

Notice I use a view for the Table attribute. I'm also returning PersonId as an Id property and concatenating FirstName and LastName together as a Name property. The UserName is the value coming back from my Authentication Type (Windows user name, email address if I'm using Forms authentication, or whatever Facebook or Twitter returns). So, there's no consistency for UserName from my Security.Authentication table.

Initializing database connection still requires a table in order for SimpleMembership to generate membership tables. There maybe a way to have a custom membership table, but I haven't figured that out yet.

ASP.Net MVC 4 Simple Membership specify Schema

2 Answers2