Microsoft SQL Server Support

You may use Microsoft SQL Server as database for TableMembershipProvider and TableRoleProvider. Any version and edition of Microsoft SQL Server should work, tested versions are 2005, 2008 and 2008 R2.

Database setup

You need to create tables, based on what providers you want to use

Membership provider

Single table (by default named Users) is required by membership provider. Use the following SQL code to create it:

CREATE TABLE dbo.Users (
    UserName                nvarchar(100)  NOT NULL,
    PasswordHash            binary(64)     NOT NULL,
    PasswordSalt            binary(128)    NOT NULL,
    Email                   nvarchar(max)  NOT NULL,
    Comment                 nvarchar(max)  NULL,
    IsApproved              bit            NOT NULL,
    DateCreated             datetime       NOT NULL,
    DateLastLogin           datetime       NULL,
    DateLastActivity        datetime       NULL,
    DateLastPasswordChange  datetime       NOT NULL,
    CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserName)
)

You may add other columns needed by your application, as long as they are either nullable or have defaults assigned. The membership provider would not touch them.

Role provider

Role provider requires two tables. First (called Roles by default) contains the role definitions. Second (called RoleMemberships by default) contains which users are members of which roles. Use the following SQL code to create the two tables:

CREATE TABLE dbo.Roles (
    RoleName                nvarchar(100)  NOT NULL,
    CONSTRAINT PK_Roles PRIMARY KEY CLUSTERED (RoleName)
)
CREATE TABLE dbo.RoleMemberships (
    UserName                nvarchar(100)  NOT NULL,
    RoleName                nvarchar(100)  NOT NULL,
    CONSTRAINT PK_RoleMemberships PRIMARY KEY CLUSTERED (UserName, RoleName),
    CONSTRAINT FK_RoleMemberships_Roles 
        FOREIGN KEY (RoleName) REFERENCES dbo.Roles (RoleName) 
        ON UPDATE CASCADE ON DELETE CASCADE,
)

Using membership and role providers together

The membership and role providers are totally independent and can be combined with any other providers. However, if you use both the TableMembershipProvider and TableRoleProvider, you might want to add foreign key constraint between their tables, so when you delete the user, its role mappings are deleted as well:

ALTER TABLE dbo.RoleMemberships 
    ADD CONSTRAINT FK_RoleMemberships_Users 
    FOREIGN KEY (UserName) REFERENCES dbo.Users (UserName) 
    ON UPDATE CASCADE ON DELETE CASCADE

Last edited Jan 23, 2011 at 9:28 PM by altair, version 1

Comments

IslandTalker Feb 21, 2011 at 6:55 AM 
Hi, Does the email have to be nvarchar(max)? Seem rather large for an email.