Thursday, November 8, 2012
Basic Membership Provider for SQL Server

Requirements

A basic membership provider requires a dedicated table to keep track of user names, passwords, and emails.

A role provider will require two tables to keep track of roles and associations of users with roles.

These are the basic membership and role provider tables with “identity” primary keys.

Basic Membership Provider database diagram using int as primary key.

SQL:

create table Users (
    UserID int identity not null primary key,
    UserName nvarchar(128) not null,
    Password nvarchar(128) not null,
    Email nvarchar(128)
)
go

create table Roles (
   RoleID int identity not null primary key,
   RoleName nvarchar(50)
)
go

create table UserRoles (
    UserID int not null,
    RoleID int not null,
    primary key(UserID, RoleID)
)
go

alter table UserRoles with check add constraint FK_UserRoles_Roles 
foreign key (RoleID) references Roles (RoleID)

alter table UserRoles with check add constraint FK_UserRoles_Users 
foreign key (UserID) references Users (UserID)

These are the basic membership and role provider tables with “unique identifier” primary keys.

Basic Membership Provider database diagram using unique identifier as primary key.

SQL:

create table Users (
    UserID uniqueidentifier not null default newid() primary key,
    UserName nvarchar(128) not null,
    Password nvarchar(128) not null,
    Email nvarchar(128)
)
go

create table Roles (
   RoleID uniqueidentifier not null default newid() primary key,
   RoleName nvarchar(50)
)
go

create table UserRoles (
    UserID uniqueidentifier not null,
    RoleID uniqueidentifier not null,
    primary key(UserID, RoleID)
)
go

alter table UserRoles with check add constraint FK_UserRoles_Roles 
foreign key (RoleID) references Roles (RoleID)

alter table UserRoles with check add constraint FK_UserRoles_Users 
foreign key (UserID) references Users (UserID)

Configuration

Use one of the scripts above to create the tables in your database.

Start Code On Time web application generator, select the project name on the start page, and choose Settings. Select Authentication and Membership.

Select “Enable custom membership and role providers” option and enter the following configuration settings.

table Users = Users
column [int|uiid] UserID = UserID
column [text] UserName = UserName
column [text] Password = Password
column [text] Email = Email

table Roles = Roles
column [int|uiid] RoleID = RoleID
column [text] RoleName = RoleName
 
table UserRoles = UserRoles
column [int|uiid] UserID = UserID
column [int|uiid] RoleID = RoleID

The configuration will guide the code generator in mapping the logical tables Users, Roles, and UserRoles to the physical tables in the database.

Generate the project to create the custom membership and role provider.