ASP.NET SQL Provider Part 2 - Tables and store procedures

ASPNETDB Database 

The structure of the SQL provider database:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Tables

Name	Description
aspnet_Applications	Used by ASP.NET features to provide an application scope for data.
aspnet_Membership	Used by the SQL Membership Provider to store membership data.
aspnet_Paths	Used by the SQL Personalization Provider to store the path for which Web Parts personalization state has been saved.
aspnet_PersonalizationAllUsers	Used by the SQL Personalization Provider to store shared personalization data.
aspnet_PersonalizationPerUser	Used by the SQL Personalization Provider to store per-user personalization data.
aspnet_Profile	Used by the SQL Profile Provider to store individual instances of property values.
aspnet_Roles	Used by the SQL Role Provider to store role data.
aspnet_SchemaVersions	Used to track the versions of schemas required by ASP.NET features.
aspnet_Users	Used to store information regarding users, including user names and IDs.
aspnet_UsersInRoles	Used by the SQL Role Provider to map roles to users.
aspnet_WebEvent_Events	Used by the SQL Web Event Provider to log event data.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Views

Name	Description
vw_aspnet_Applications	Displays information for all applications.
vw_aspnet_MembershipUsers	Displays a list of ASP.NET membership users associated with the unique identifier for the user.
vw_aspnet_Profiles	Displays user profile information.
vw_aspnet_Roles	Displays role information.
vw_aspnet_Users	Displays a list of users per application.
vw_aspnet_UsersInRoles	Displays which users are associated with which roles by the unique identifiers for the user and the role.
vw_aspnet_WebPartState_Paths	Displays Web Parts state path information.
vw_aspnet_WebPartState_Shared	Displays Web Parts state information.
vw_aspnet_WebPartState_User	Displays Web Parts user information.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The aspnet_Applications table

 

Column Name	Column Type	Description
ApplicationId	uniqueidentifier	Application ID
ApplicationName	nvarchar(256)	Application name
LoweredApplicationName	nvarchar(256)	Application name (lowercase)
Description	nvarchar(256)	Application descriptio

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Applications	ApplicationId	aspnet_Users	ApplicationId
aspnet_Applications	ApplicationId	aspnet_Membership	ApplicationId
aspnet_Applications	ApplicationId	aspnet_Roles	ApplicationId
aspnet_Applications	ApplicationId	aspnet_Paths	ApplicationId

Referencing Views

Name
vw_aspnet_Applications

The aspnet_Membership table

SqlMembershipProvider stores membership data in the aspnet_Membership table of the provider database. Each record in aspnet_Membership corresponds to one membership user.

Column Name	Column Type	Description
ApplicationId	uniqueidentifier	Application ID
UserId	uniqueidentifier	User ID
Password	nvarchar(128)	Password (plaintext, hashed, or encrypted; base-64-encoded if hashed or encrypted)
PasswordFormat	int	Password format (0=Plaintext, 1=Hashed, 2=Encrypted)
PasswordSalt	nvarchar(128)	Randomly generated 128-bit value used to salt password hashes; stored in base-64-encoded form
MobilePIN	nvarchar(16)	User's mobile PIN (currently not used)
Email	nvarchar(256)	User's e-mail address
LoweredEmail	nvarchar(256)	User's e-mail address (lowercase)
PasswordQuestion	nvarchar(256)	Password question
PasswordAnswer	nvarchar(128)	Answer to password question
IsApproved	bit	1=Approved, 0=Not approved
IsLockedOut	bit	1=Locked out, 0=Not locked out
CreateDate	datetime	Date and time this account was created
LastLoginDate	datetime	Date and time of this user's last login
LastPasswordChangedDate	datetime	Date and time this user's password was last changed
LastLockoutDate	datetime	Date and time this user was last locked out
FailedPasswordAttemptCount	int	Number of consecutive failed login attempts
FailedPasswordAttempt-WindowStart	datetime	Date and time of first failed login if FailedPasswordAttemptCount is nonzero
FailedPasswordAnswer-AttemptCount	int	Number of consecutive failed password answer attempts
FailedPasswordAnswer-AttemptWindowStart	datetime	Date and time of first failed password answer if FailedPasswordAnswerAttemptCount is nonzero
Comment	ntext	Additional text

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Applications	ApplicationId	aspnet_Membership	ApplicationId
aspnet_Users	UserId	aspnet_Membership	UserId

 

Referencing Views

Name
vw_aspnet_MembershipUsers

The aspnet_Users table

Column Name	Column Type	Description
ApplicationId	uniqueidentifier	Application ID
UserId	uniqueidentifier	User ID
UserName	nvarchar(256)	User name
LoweredUserName	nvarchar(256)	User name (lowercase)
MobileAlias	nvarchar(16)	User's mobile alias (currently not used)
IsAnonymous	bit	1=Anonymous user, 0=Not an anonymous user
LastActivityDate	datetime	Date and time of last activity by this user

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Applications	ApplicationId	aspnet_Users	ApplicationId
aspnet_Users	UserId	aspnet_Membership	UserId
aspnet_Users	UserId	aspnet_Profile	UserId
aspnet_Users	UserId	aspnet_UsersInRoles	UserId
aspnet_Users	UserId	aspnet_PersonalizationPerUser	UserId

Referencing Views

Name
vw_aspnet_MembershipUsers
vw_aspnet_Users

Stored procedures used by SqlMembershipProvider

Stored Procedure	Description
aspnet_Membership_ChangePassword-QuestionAndAnswer	Changes the specified user's password question and answer.
aspnet_Membership_CreateUser	Adds a new membership user to the membership database. Records the user in the aspnet_Users and aspnet_Membership tables and, if necessary, adds a new application to the aspnet_Applications table.
aspnet_Membership_FindUsersByEmail	Retrieves records from aspnet_Membership table with e-mail addresses matching the specified pattern and with the specified application ID.
aspnet_Membership_FindUsersByName	Retrieves records from aspnet_Membership table with user names matching the specified pattern and with the specified application ID.
aspnet_Membership_GetAllUsers	Retrieves all users from the aspnet_Membership table with the specified application ID.
aspnet_Membership_GetNumberOfUsersOnline	Gets the number of users currently online (those whose last activity dates.
aspnet_Membership_GetPassword	Gets the specified user's password data from the database. Used for retrieving passwords with a user-supplied password answer.
aspnet_Membership_GetPasswordWithFormat	Gets the specified user's password from the database. Used by the provider to retrieve passwords for performing password comparisons (for example, when ValidateUser needs to validate a password).
aspnet_Membership_GetUserByEmail	Given an e-mail address and application ID, retrieves the corresponding record from the aspnet_Membership table.
aspnet_Membership_GetUserByName	Given a user name and application ID, retrieves the corresponding record from the aspnet_Membership table.
aspnet_Membership_GetUserByUserId	Given a user ID and application ID, retrieves the corresponding record from the aspnet_Membership table.
aspnet_Membership_ResetPassword	Resets the specified user's password based on a password answer.
aspnet_Membership_SetPassword	Sets the specified user's password to the password input to the stored procedure.
aspnet_Membership_UnlockUser	Restores login privileges for the specified user by setting the user's IsLockedOut bit to 0.
aspnet_Membership_UpdateUser	Updates the user's last activity date in the aspnet_Users table and e-mail address, comment, is-approved status, and last login date in the aspnet_Membership table.
aspnet_Membership_UpdateUserInfo	Updates account locking data for the specified user in the aspnet_Users and aspnet_Membership tables. Used in conjunction with provider methods that track bad password and bad password-answer attempts.
aspnet_Users_CreateUser	Adds a user to the aspnet_Users table. Called by aspnet_Membership_CreateUser.
aspnet_Users_DeleteUser	Deletes a user from the aspnet_Membership table and optionally from other SQL provider tables, including aspnet_Users.

Relationships

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The aspnet_Roles table

Column Name	Column Type	Description
ApplicationId	uniqueidentifier	Application ID
RoleId	uniqueidentifier	Role ID
RoleName	nvarchar(256)	Role name
LoweredRoleName	nvarchar(256)	Role name (lowercase)
Description	nvarchar(256)	Role description (currently unused)

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Applications	ApplicationId	aspnet_Roles	ApplicationId
aspnet_Roles	RoleId	aspnet_UsersInRoles	RoleId

Referencing Views

Name
vw_aspnet_Roles

The aspnet_UsersInRoles table

Column Name	Column Type	Description
UserId	uniqueidentifier	User ID
RoleId	uniqueidentifier	Role ID

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Users	UserId	aspnet_UsersInRoles	UserId
aspnet_Roles	RoleId	aspnet_UsersInRoles	RoleId

Referencing Views

Name
vw_aspnet_UsersInRoles

Stored procedures used by SqlRoleProvider

Stored Procedure	Description
aspnet_Roles_CreateRole	Adds a role to the aspnet_Roles table and, if necessary, adds a new application to the aspnet_Applications table.
aspnet_Roles_DeleteRole	Removes a role from the aspnet_Roles table. Optionally deletes records referencing the deleted role from the aspnet_UsersInRoles table.
aspnet_Roles_GetAllRoles	Retrieves all roles with the specified application ID from the aspnet_Roles table.
aspnet_Roles_RoleExists	Checks the aspnet_Roles table to determine whether the specified role exists.
aspnet_UsersInRoles_AddUsersToRoles	Adds the specified users to the specified roles by adding them to the aspnet_UsersInRoles table.
aspnet_UsersInRoles_FindUsersInRole	Queries the aspnet_UsersInRoles table for all users belonging to the specified role whose user names match the specified pattern.
aspnet_UsersInRoles_GetRolesForUser	Queries the aspnet_UsersInRoles table for all roles assigned to a specified user.
aspnet_UsersInRoles_GetUsersInRoles	Queries the aspnet_UsersInRoles table for all users belonging to the specified role.
aspnet_UsersInRoles_IsUserInRole	Checks the aspnet_UsersInRoles table to determine whether the specified user belongs to the specified role.
aspnet_UsersInRoles_RemoveUsersFromRoles	Removes the specified users from the specified roles by deleting the corresponding records from the aspnet_UsersInRoles table.

Relationships: membership and roles 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The aspnet_Profile table

Column Name	Column Type	Description
UserId	uniqueidentifier	ID of the user to which this profile data pertains
PropertyNames	ntext	Names of all property values stored in this profile
PropertyValuesString	ntext	Values of properties that could be persisted as text
PropertyValuesBinary	image	Values of properties that were configured to use binary serialization
LastUpdatedDate	datetime	Date and time this profile was last updated

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Users	UserId	aspnet_Profile	UserId

Referencing Views

Name
vw_aspnet_Profiles

Stored procedures used by SqlProfileProvider

Stored Procedure	Description
aspnet_Profile_DeleteInactiveProfiles	Deletes profile data from the aspnet_Profile table for users whose last activity dates in the aspnet_Users table fall on or before the specified date.
aspnet_Profile_DeleteProfiles	Deletes profile data from the aspnet_Profile table for the specified users.
aspnet_Profile_GetNumberOfInactiveProfiles	Queries the aspnet_Profile table to get a count of profiles whose last activity dates (in the aspnet_Users table) fall on or before the specified date.
aspnet_Profile_GetProfiles	Retrieves profile data from the aspnet_Profile table for users who match the criteria input to the stored procedure.
aspnet_Profile_GetProperties	Retrieves profile data for the specified user.
aspnet_Profile_SetProperties	Saves profile data for the specified user.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The aspnet_PersonalizationPerUser table

Column Name	Column Type	Description
Id	uniqueidentifier	ID of this record
PathId	uniqueidentifier	ID of the virtual path to which this state pertains
UserId	uniqueidentifier	ID of the user to which this state pertains
PageSettings	image	Serialized personalization state
LastUpdatedDate	datetime	Date and time state was saved

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Paths	PathId	aspnet_PersonalizationPerUser	PathId
aspnet_Users	UserId	aspnet_PersonalizationPerUser	UserId

Referencing Views

Name
vw_aspnet_WebPartState_User

The aspnet_PersonalizationAllUsers table

Column Name	Column Type	Description
PathId	uniqueidentifier	ID of the virtual path to which this state pertains
PageSettings	image	Serialized personalization state
LastUpdatedDate	datetime	Date and time state was saved

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Paths	PathId	aspnet_PersonalizationAllUsers	PathId

Referencing Views

Name
vw_aspnet_WebPartState_Shared

The aspnet_Paths table

Column Name	Column Type	Description
ApplicationId	uniqueidentifier	Application ID
PathId	uniqueidentifier	Path ID
Path	nvarchar(256)	Path name
LoweredPath	nvarchar(256)	Path name (lowercase)

Relationships

Primary Table	Primary Key	Foreign Table	Foreign Key
aspnet_Applications	ApplicationId	aspnet_Paths	ApplicationId
aspnet_Paths	PathId	aspnet_PersonalizationAllUsers	PathId
aspnet_Paths	PathId	aspnet_PersonalizationPerUser	PathId

Referencing Views

Name
vw_aspnet_WebPartState_Paths

Stored procedures used by SqlPersonalizationProvider

Stored Procedure	Description
aspnet_PersonalizationAdministration_DeleteAllState	Deletes all records from aspnet_PersonalizationAllUsers or aspnet_PersonalizationPerUser corresponding to the specified application ID.
aspnet_PersonalizationAdministration_FindState	Retrieves profile data from aspnet_PersonalizationAllUsers or aspnet_PersonalizationPerUser meeting several input criteria.
aspnet_PersonalizationAdministration_GetCountOfState	Returns a count of records in the aspnet_PersonalizationAllUsers table with path names matching the specified pattern, or a count of records in the aspnet_PersonalizationPerUser table meeting several input criteria.
aspnet_PersonalizationAdministration_ResetSharedState	Resets shared state for the specified page, by deleting the corresponding record from the aspnet_PersonalizationAllUsers table.
aspnet_PersonalizationAdministration_ResetUserState	Resets per-user state for the specified user and the specified page, by deleting the corresponding record from the aspnet_PersonalizationPerUser table. Can also delete records, based on the user's last activity date if it falls on or before the specified date.
aspnet_PersonalizationAllUsers_GetPageSettings	Retrieves shared state for the specified page from the aspnet_PersonalizationAllUsers table.
aspnet_PersonalizationAllUsers_ResetPageSettings	Resets shared state for the specified page, by deleting the corresponding record from the aspnet_PersonalizationAllUsers table.
aspnet_PersonalizationAllUsers_SetPageSettings	Saves shared state for the specified page in the aspnet_PersonalizationAllUsers table.
aspnet_PersonalizationPerUser_GetPageSettings	Retrieves per-user state for the specified page and the specified user from the aspnet_PersonalizationPerUser table.
aspnet_PersonalizationPerUser_ResetPageSettings	Resets per-user state for the specified page and the specified user, by deleting the corresponding record from the aspnet_PersonalizationPerUser table.
aspnet_PersonalizationPerUser_SetPageSettings	Saves per-user state for the specified page and the specified user in the aspnet_PersonalizationPerUser table.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The aspnet_WebEvent_Events table

Column Name	Column Type	Description
EventId	char(32)	Event ID (from WebBaseEvent.EventId)
EventTimeUtc	datetime	UTC time at which the event was fired (from WebBaseEvent.EventTimeUtc)
EventTime	datetime	Local time at which the event was fired (from WebBaseEvent.EventTime)
EventType	nvarchar(256)	Event type (for example, WebFailureAuditEvent)
EventSequence	decimal(19,0)	Event sequence number (from WebBaseEvent.EventSequence)
EventOccurrence	decimal(19,0)	Event occurrence count (from WebBaseEvent.EventOccurrence)
EventCode	int	Event code (from WebBaseEvent.EventCode)
EventDetailCode	int	Event detail code (from WebBaseEvent.EventDetailCode)
Message	nvarchar(1024)	Event message (from WebBaseEvent.EventMessage)
ApplicationPath	nvarchar(256)	Physical path of the application that generated the Web event (for example, C:\Websites\MyApp)
ApplicationVirtualPath	nvarchar(256)	Virtual path of the application that generated the event (for example, /MyApp)
MachineName	nvarchar(256)	Name of the machine on which the event was generated
RequestUrl	nvarchar(1024)	URL of the request that generated the Web event
ExceptionType	nvarchar(256)	If the Web event is a WebBaseErrorEvent, type of exception recorded in the ErrorException property; otherwise, DBNull
Details	ntext	Text generated by calling ToString on the Web event

Stored procedure used by SqlWebEventProvider

Stored Procedure	Description
aspnet_WebEvent_LogEvent	Records a Web event in the aspnet_WebEvents_Events table.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The aspnet_SchemaVersions table

Name	Type	Required?	Defaults To	Description
Feature	nvarchar(128)	Yes		Name of the application feature
CompatibleSchemaVersion	nvarchar(128)	Yes		Schema version required for compatibility
IsCurrentVersion	bit	Yes		1=Current version, 0=Not current version

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------