Professional ASP.NET 3.5 Security, Membership, and Role Management with C# and VB

12.2. The Membership Database Schema

The Membership database schema (contained in InstallMembership.sql) deals with storing Membership-specific data. Where overlaps exist with the common table schema (the username and application name), the data is stored using the common tables. As a result, only one additional table is added by Membership: the aspnet_Membership table. There is also a view called vw_aspnet_MembershipUsers that maps most, though not all, of the columns on this table. The vast majority of the Membership database schemas that are installed are for stored procedures used by SqlMembershipProvider.

The aspnet_Membership table is:

CREATE TABLE dbo.aspnet_Membership (
ApplicationId                           uniqueidentifier    NOT NULL
                  FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
UserId                                  uniqueidentifier    NOT NULL
                  PRIMARY KEY NONCLUSTERED
                  FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),
Password                                nvarchar(128)       NOT NULL,
PasswordFormat                          int                 NOT NULL DEFAULT 0,
PasswordSalt                            nvarchar(128)       NOT NULL,
MobilePIN                               nvarchar(16),
Email                                   nvarchar(256),
LoweredEmail                            nvarchar(256),
PasswordQuestion                        nvarchar(256),

PasswordAnswer nvarchar(128), IsApproved bit NOT NULL, IsLockedOut bit NOT NULL, CreateDate datetime NOT NULL, LastLoginDate datetime NOT NULL, LastPasswordChangedDate datetime NOT NULL, LastLockoutDate datetime NOT NULL, FailedPasswordAttemptCount int NOT NULL, FailedPasswordAttemptWindowStart datetime NOT NULL, FailedPasswordAnswerAttemptCount int NOT NULL, FailedPasswordAnswerAttemptWindowStart ...