ASPNETDB Database

Table of Contents

ASPNETDB Database

Description

ASP.NET 2.0 Provider Database

Remarks

The ASPNETDB is used by ASP.NET 2.0 providers to persist state in SQL Server.  This database is typically created using the aspnet_regsql.exe tool that comes with ASP.NET.

ASP.NET 2.0 incluides the following types of providers:

For more information about ASP.NET 2.0 Providers, please visit the following MSDN article:

Microsoft ASP.NET 2.0 Providers

 

See Also

Tables | Views | Procedures

Tables: ASPNETDB

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.

Table: aspnet_Applications

Description

Used by ASP.NET features to provide an application scope for data.

Columns

Name  Type  Required?  Defaults To  Description 
ApplicationName  nvarchar(256)  Yes    Application name
LoweredApplicationName  nvarchar(256)  Yes    Application name (lowercase)
ApplicationId  uniqueidentifier  Yes  (newid())  Application ID
Description  nvarchar(256)  No    Application description

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 

Indexes

Name  Type  Columns 
aspnet_Applications_Index  Non-unique, Clustered  LoweredApplicationName 
PK__aspnet_Applicati__7E6CC920  Unique  ApplicationId 
UQ__aspnet_Applicati__00551192  Unique  ApplicationName 
UQ__aspnet_Applicati__7F60ED59  Unique  LoweredApplicationName 

Referencing Views

Name 
vw_aspnet_Applications 

Definition

CREATE TABLE [aspnet_Applications]
(
    [ApplicationName] nvarchar(256) NOT NULL,
    [LoweredApplicationName] nvarchar(256) NOT NULL,
    [ApplicationId] uniqueidentifier NOT NULL DEFAULT ((newid())),
    [Description] nvarchar(256) NULL
    CONSTRAINT [PK_aspnet_Applications] PRIMARY KEY
    (
        [ApplicationId]
    )
)

Table: aspnet_Membership

Description

Used by the SQL Membership Provider to store membership data.

Columns

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

Relationships

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

Indexes

Name  Type  Columns 
aspnet_Membership_index  Non-unique, Clustered  ApplicationId, LoweredEmail 
PK__aspnet_Membershi__1367E606  Unique  UserId 

Referencing Views

Name 
vw_aspnet_MembershipUsers 

Definition

CREATE TABLE [aspnet_Membership]
(
    [ApplicationId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL,
    [Password] nvarchar(128) NOT NULL,
    [PasswordFormat] int NOT NULL DEFAULT (((0))),
    [PasswordSalt] nvarchar(128) NOT NULL,
    [MobilePIN] nvarchar(16) NULL,
    [Email] nvarchar(256) NULL,
    [LoweredEmail] nvarchar(256) NULL,
    [PasswordQuestion] nvarchar(256) NULL,
    [PasswordAnswer] nvarchar(128) NULL,
    [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] datetime NOT NULL,
    [Comment] ntext NULL
    CONSTRAINT [PK_aspnet_Membership] PRIMARY KEY
    (
        [UserId]
    )
)

Table: aspnet_Paths

Description

Used by the SQL Personalization Provider to store the path for which Web Parts personalization state has been saved.

Columns

Name  Type  Required?  Defaults To  Description 
ApplicationId  uniqueidentifier  Yes    Application ID
PathId  uniqueidentifier  Yes  (newid())  Path ID
Path  nvarchar(256)  Yes    Path name
LoweredPath  nvarchar(256)  Yes    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 

Indexes

Name  Type  Columns 
aspnet_Paths_index  Unique, Clustered  ApplicationId, LoweredPath 
PK__aspnet_Paths__44FF419A  Unique  PathId 

Referencing Views

Name 
vw_aspnet_WebPartState_Paths 

Definition

CREATE TABLE [aspnet_Paths]
(
    [ApplicationId] uniqueidentifier NOT NULL,
    [PathId] uniqueidentifier NOT NULL DEFAULT ((newid())),
    [Path] nvarchar(256) NOT NULL,
    [LoweredPath] nvarchar(256) NOT NULL
    CONSTRAINT [PK_aspnet_Paths] PRIMARY KEY
    (
        [PathId]
    )
)

Table: aspnet_PersonalizationAllUsers

Description

Used by the SQL Personalization Provider to store shared personalization data.

Columns

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

Relationships

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

Indexes

Name  Type  Columns 
PK__aspnet_Personali__4AB81AF0  Unique, Clustered  PathId 

Referencing Views

Name 
vw_aspnet_WebPartState_Shared 

Definition

CREATE TABLE [aspnet_PersonalizationAllUsers]
(
    [PathId] uniqueidentifier NOT NULL,
    [PageSettings] image NOT NULL,
    [LastUpdatedDate] datetime NOT NULL
    CONSTRAINT [PK_aspnet_PersonalizationAllUsers] PRIMARY KEY
    (
        [PathId]
    )
)

Table: aspnet_PersonalizationPerUser

Description

Used by the SQL Personalization Provider to store per-user personalization data.

Columns

Name  Type  Required?  Defaults To  Description 
Id  uniqueidentifier  Yes  (newid())  ID of this record
PathId  uniqueidentifier  No    ID of the virtual path to which this state pertains
UserId  uniqueidentifier  No    ID of the user to which this state pertains
PageSettings  image  Yes    Serialized personalization state
LastUpdatedDate  datetime  Yes    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 

Indexes

Name  Type  Columns 
aspnet_PersonalizationPerUser_index1  Unique, Clustered  PathId, UserId 
aspnet_PersonalizationPerUser_ncindex2  Unique  UserId, PathId 
PK__aspnet_Personali__4D94879B  Unique  Id 

Referencing Views

Name 
vw_aspnet_WebPartState_User 

Definition

CREATE TABLE [aspnet_PersonalizationPerUser]
(
    [Id] uniqueidentifier NOT NULL DEFAULT ((newid())),
    [PathId] uniqueidentifier NULL,
    [UserId] uniqueidentifier NULL,
    [PageSettings] image NOT NULL,
    [LastUpdatedDate] datetime NOT NULL
    CONSTRAINT [PK_aspnet_PersonalizationPerUser] PRIMARY KEY
    (
        [Id]
    )
)

Table: aspnet_Profile

Description

Used by the SQL Profile Provider to store individual instances of property values.

Columns

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

Relationships

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

Indexes

Name  Type  Columns 
PK__aspnet_Profile__286302EC  Unique, Clustered  UserId 

Referencing Views

Name 
vw_aspnet_Profiles 

Definition

CREATE TABLE [aspnet_Profile]
(
    [UserId] uniqueidentifier NOT NULL,
    [PropertyNames] ntext NOT NULL,
    [PropertyValuesString] ntext NOT NULL,
    [PropertyValuesBinary] image NOT NULL,
    [LastUpdatedDate] datetime NOT NULL
    CONSTRAINT [PK_aspnet_Profile] PRIMARY KEY
    (
        [UserId]
    )
)

Table: aspnet_Roles

Description

Used by the SQL Role Provider to store role data.

Columns

Name  Type  Required?  Defaults To  Description 
ApplicationId  uniqueidentifier  Yes    Application ID
RoleId  uniqueidentifier  Yes  (newid())  Role ID
RoleName  nvarchar(256)  Yes    Role name
LoweredRoleName  nvarchar(256)  Yes    Role name (lowercase)
Description  nvarchar(256)  No    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 

Indexes

Name  Type  Columns 
aspnet_Roles_index1  Unique, Clustered  ApplicationId, LoweredRoleName 
PK__aspnet_Roles__31EC6D26  Unique  RoleId 

Referencing Views

Name 
vw_aspnet_Roles 

Definition

CREATE TABLE [aspnet_Roles]
(
    [ApplicationId] uniqueidentifier NOT NULL,
    [RoleId] uniqueidentifier NOT NULL DEFAULT ((newid())),
    [RoleName] nvarchar(256) NOT NULL,
    [LoweredRoleName] nvarchar(256) NOT NULL,
    [Description] nvarchar(256) NULL
    CONSTRAINT [PK_aspnet_Roles] PRIMARY KEY
    (
        [RoleId]
    )
)

Table: aspnet_SchemaVersions

Description

Used to track the versions of schemas required by ASP.NET features.

Columns

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

Indexes

Name  Type  Columns 
PK__aspnet_SchemaVer__08EA5793  Unique, Clustered  Feature, CompatibleSchemaVersion 

Definition

CREATE TABLE [aspnet_SchemaVersions]
(
    [Feature] nvarchar(128) NOT NULL,
    [CompatibleSchemaVersion] nvarchar(128) NOT NULL,
    [IsCurrentVersion] bit NOT NULL
    CONSTRAINT [PK_aspnet_SchemaVersions] PRIMARY KEY
    (
        [Feature],
        [CompatibleSchemaVersion]
    )
)

Table: aspnet_Users

Description

Used to store information regarding users, including user names and IDs.

Columns

Name  Type  Required?  Defaults To  Description 
ApplicationId  uniqueidentifier  Yes    Application ID
UserId  uniqueidentifier  Yes  (newid())  User ID
UserName  nvarchar(256)  Yes    User name
LoweredUserName  nvarchar(256)  Yes    User name (lowercase)
MobileAlias  nvarchar(16)  No  (NULL)  User's mobile alias (currently not used)
IsAnonymous  bit  Yes  ((0))  1=Anonymous user, 0=Not an anonymous user
LastActivityDate  datetime  Yes    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 

Indexes

Name  Type  Columns 
aspnet_Users_Index  Unique, Clustered  ApplicationId, LoweredUserName 
aspnet_Users_Index2  Non-unique  ApplicationId, LastActivityDate 
PK__aspnet_Users__03317E3D  Unique  UserId 

Referencing Views

Name 
vw_aspnet_MembershipUsers 
vw_aspnet_Users 

Definition

CREATE TABLE [aspnet_Users]
(
    [ApplicationId] uniqueidentifier NOT NULL,
    [UserId] uniqueidentifier NOT NULL DEFAULT ((newid())),
    [UserName] nvarchar(256) NOT NULL,
    [LoweredUserName] nvarchar(256) NOT NULL,
    [MobileAlias] nvarchar(16) NULL DEFAULT ((NULL)),
    [IsAnonymous] bit NOT NULL DEFAULT (((0))),
    [LastActivityDate] datetime NOT NULL
    CONSTRAINT [PK_aspnet_Users] PRIMARY KEY
    (
        [UserId]
    )
)

Table: aspnet_UsersInRoles

Description

Used by the SQL Role Provider to map roles to users.

Columns

Name  Type  Required?  Defaults To  Description 
UserId  uniqueidentifier  Yes    User ID
RoleId  uniqueidentifier  Yes    Role ID

Relationships

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

Indexes

Name  Type  Columns 
aspnet_UsersInRoles_index  Non-unique  RoleId 
PK__aspnet_UsersInRo__35BCFE0A  Unique, Clustered  UserId, RoleId 

Referencing Views

Name 
vw_aspnet_UsersInRoles 

Definition

CREATE TABLE [aspnet_UsersInRoles]
(
    [UserId] uniqueidentifier NOT NULL,
    [RoleId] uniqueidentifier NOT NULL
    CONSTRAINT [PK_aspnet_UsersInRoles] PRIMARY KEY
    (
        [UserId],
        [RoleId]
    )
)

Table: aspnet_WebEvent_Events

Description

Used by the SQL Web Event Provider to log event data.

Columns

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

Indexes

Name  Type  Columns 
PK__aspnet_WebEvent___5FB337D6  Unique, Clustered  EventId 

Definition

CREATE TABLE [aspnet_WebEvent_Events]
(
    [EventId] char(32) NOT NULL,
    [EventTimeUtc] datetime NOT NULL,
    [EventTime] datetime NOT NULL,
    [EventType] nvarchar(256) NOT NULL,
    [EventSequence] decimal(19) NOT NULL,
    [EventOccurrence] decimal(19) NOT NULL,
    [EventCode] int NOT NULL,
    [EventDetailCode] int NOT NULL,
    [Message] nvarchar(1024) NULL,
    [ApplicationPath] nvarchar(256) NULL,
    [ApplicationVirtualPath] nvarchar(256) NULL,
    [MachineName] nvarchar(256) NOT NULL,
    [RequestUrl] nvarchar(1024) NULL,
    [ExceptionType] nvarchar(256) NULL,
    [Details] ntext NULL
    CONSTRAINT [PK_aspnet_WebEvent_Events] PRIMARY KEY
    (
        [EventId]
    )
)

Views: ASPNETDB

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.

View: vw_aspnet_Applications

Description

Displays information for all applications.

Columns

Name  Type  Required?  Defaults To  Description 
ApplicationName  nvarchar  Yes    Application name
LoweredApplicationName  nvarchar  Yes    Application name (lowercase)
ApplicationId  uniqueidentifier  Yes    Application ID
Description  nvarchar  No    Application description

Tables Referenced

Name 
aspnet_Applications 

Definition

SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
  FROM [dbo].[aspnet_Applications]

View: vw_aspnet_MembershipUsers

Description

Displays a list of ASP.NET membership users associated with the unique identifier for the user.

Columns

Name  Type  Required?  Defaults To  Description 
UserId  uniqueidentifier  Yes    User ID
PasswordFormat  int  Yes    Password format (0=Plaintext, 1=Hashed, 2=Encrypted)
MobilePIN  nvarchar  No    User's mobile PIN (currently not used)
Email  nvarchar  No    User's e-mail address
LoweredEmail  nvarchar  No    User's e-mail address (lowercase)
PasswordQuestion  nvarchar  No    Password question
PasswordAnswer  nvarchar  No    Answer to password question
IsApproved  bit  Yes    1=Approved, 0=Not approved
IsLockedOut  bit  Yes    1=Locked out, 0=Not locked out
CreateDate  datetime  Yes    Date and time this account was created
LastLoginDate  datetime  Yes    Date and time of this user's last login
LastPasswordChangedDate  datetime  Yes    Date and time this user's password was last changed
LastLockoutDate  datetime  Yes    Date and time this user was last locked out
FailedPasswordAttemptCount  int  Yes    Number of consecutive failed login attempts
FailedPasswordAttemptWindowStart  datetime  Yes    Date and time of first failed login if FailedPasswordAttemptCount is nonzero
FailedPasswordAnswerAttemptCount  int  Yes    Number of consecutive failed password answer attempts
FailedPasswordAnswerAttemptWindowStart  datetime  Yes    Date and time of first failed password answer if FailedPasswordAnswerAttemptCount is nonzero
Comment  ntext  No    Additional text
ApplicationId  uniqueidentifier  Yes    Application ID
UserName  nvarchar  Yes    User name
MobileAlias  nvarchar  No    User's mobile alias (currently not used)
IsAnonymous  bit  Yes    1=Anonymous user, 0=Not an anonymous user
LastActivityDate  datetime  Yes    Date and time of last activity by this user

Tables Referenced

Name 
aspnet_Membership 
aspnet_Users 

Definition

SELECT [dbo].[aspnet_Membership].[UserId],
            [dbo].[aspnet_Membership].[PasswordFormat],
            [dbo].[aspnet_Membership].[MobilePIN],
            [dbo].[aspnet_Membership].[Email],
            [dbo].[aspnet_Membership].[LoweredEmail],
            [dbo].[aspnet_Membership].[PasswordQuestion],
            [dbo].[aspnet_Membership].[PasswordAnswer],
            [dbo].[aspnet_Membership].[IsApproved],
            [dbo].[aspnet_Membership].[IsLockedOut],
            [dbo].[aspnet_Membership].[CreateDate],
            [dbo].[aspnet_Membership].[LastLoginDate],
            [dbo].[aspnet_Membership].[LastPasswordChangedDate],
            [dbo].[aspnet_Membership].[LastLockoutDate],
            [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
            [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
            [dbo].[aspnet_Membership].[Comment],
            [dbo].[aspnet_Users].[ApplicationId],
            [dbo].[aspnet_Users].[UserName],
            [dbo].[aspnet_Users].[MobileAlias],
            [dbo].[aspnet_Users].[IsAnonymous],
            [dbo].[aspnet_Users].[LastActivityDate]
  FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]

View: vw_aspnet_Profiles

Description

Displays user profile information.

Columns

Name  Type  Required?  Defaults To  Description 
UserId  uniqueidentifier  Yes    ID of the user to which this profile data pertains
LastUpdatedDate  datetime  Yes    Date and time this profile was last updated
DataSize  int  No    Size of the profile data

Tables Referenced

Name 
aspnet_Profile 

Definition

SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
  FROM [dbo].[aspnet_Profile]

View: vw_aspnet_Roles

Description

Displays role information.

Columns

Name  Type  Required?  Defaults To  Description 
ApplicationId  uniqueidentifier  Yes    Application ID
RoleId  uniqueidentifier  Yes    Role ID
RoleName  nvarchar  Yes    Role name
LoweredRoleName  nvarchar  Yes    Role name (lowercase)
Description  nvarchar  No    Role description (currently unused)

Tables Referenced

Name 
aspnet_Roles 

Definition

SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
  FROM [dbo].[aspnet_Roles]

View: vw_aspnet_Users

Description

Displays a list of users per application.

Columns

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

Tables Referenced

Name 
aspnet_Users 

Definition

SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]
  FROM [dbo].[aspnet_Users]

View: vw_aspnet_UsersInRoles

Description

Displays which users are associated with which roles by the unique identifiers for the user and the role.

Columns

Name  Type  Required?  Defaults To  Description 
UserId  uniqueidentifier  Yes    User ID
RoleId  uniqueidentifier  Yes    Role ID

Tables Referenced

Name 
aspnet_UsersInRoles 

Definition

SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
  FROM [dbo].[aspnet_UsersInRoles]

View: vw_aspnet_WebPartState_Paths

Description

Displays Web Parts state path information.

Columns

Name  Type  Required?  Defaults To  Description 
ApplicationId  uniqueidentifier  Yes    Application ID
PathId  uniqueidentifier  Yes    Path ID
Path  nvarchar  Yes    Path name
LoweredPath  nvarchar  Yes    Path name (lowercase)

Tables Referenced

Name 
aspnet_Paths 

Definition

SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
  FROM [dbo].[aspnet_Paths]

View: vw_aspnet_WebPartState_Shared

Description

Displays Web Parts state information.

Columns

Name  Type  Required?  Defaults To  Description 
PathId  uniqueidentifier  Yes    ID of the virtual path to which this state pertains
DataSize  int  No    Size of the data
LastUpdatedDate  datetime  Yes    Date and time state was saved

Tables Referenced

Name 
aspnet_PersonalizationAllUsers 

Definition

SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationAllUsers]

View: vw_aspnet_WebPartState_User

Description

Displays Web Parts user information.

Columns

Name  Type  Required?  Defaults To  Description 
PathId  uniqueidentifier  No    ID of the virtual path to which this state
UserId  uniqueidentifier  No    ID of the user to which this state pertains
DataSize  int  No    Size of the user-scoped data
LastUpdatedDate  datetime  Yes    Date and time state was saved

Tables Referenced

Name 
aspnet_PersonalizationPerUser 

Definition

SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationPerUser]

Procedures: ASPNETDB

Procedures

Name  Description 
aspnet_AnyDataInTables  Checks to see if there is any data in the specified tables.
aspnet_Applications_CreateApplication  Adds a new application to the aspnet_Application table.
aspnet_CheckSchemaVersion  Checks the compatibility of the schema version for the given feature.
aspnet_Membership_ChangePasswordQuestionAndAnswer  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 email 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, isapproved 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_Paths_CreatePath  Retrieves a path ID from the aspnet_Paths table, or creates a new one if the specified path doesn't exist.
aspnet_Personalization_GetApplicationId  Converts the application name input to it into an application ID.
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.
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.
aspnet_RegisterSchemaVersion  Registers the compatible schema required for the given feature.
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_Setup_RemoveAllRoleMembers  Removes all roles from the given SQL account.
aspnet_Setup_RestorePermissions  Restores permissions to the given SQL account.
aspnet_UnRegisterSchemaVersion  Unregisters the schema version for the given feature.
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.
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.
aspnet_WebEvent_LogEvent  Records a Web event in the aspnet_WebEvents_Events table.

Procedure: aspnet_AnyDataInTables

Description

Checks to see if there is any data in the specified tables.

Parameters

Name  Type  Direction 
@TablesToCheck  int  Input 

Definition

CREATE PROCEDURE [dbo].aspnet_AnyDataInTables
    @TablesToCheck int
AS
BEGIN
    -- Check Membership table if (@TablesToCheck & 1) is set
    IF ((@TablesToCheck & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
        BEGIN
            SELECT N'aspnet_Membership'
            RETURN
        END
    END

    -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
    IF ((@TablesToCheck & 2) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
        BEGIN
            SELECT N'aspnet_Roles'
            RETURN
        END
    END

    -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
    IF ((@TablesToCheck & 4) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
        BEGIN
            SELECT N'aspnet_Profile'
            RETURN
        END
    END

    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
    IF ((@TablesToCheck & 8) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
        BEGIN
            SELECT N'aspnet_PersonalizationPerUser'
            RETURN
        END
    END

    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
    IF ((@TablesToCheck & 16) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
        BEGIN
            SELECT N'aspnet_WebEvent_Events'
            RETURN
        END
    END

    -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
    IF ((@TablesToCheck & 1) <> 0 AND
        (@TablesToCheck & 2) <> 0 AND
        (@TablesToCheck & 4) <> 0 AND
        (@TablesToCheck & 8) <> 0 AND
        (@TablesToCheck & 32) <> 0 AND
        (@TablesToCheck & 128) <> 0 AND
        (@TablesToCheck & 256) <> 0 AND
        (@TablesToCheck & 512) <> 0 AND
        (@TablesToCheck & 1024) <> 0)
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
        BEGIN
            SELECT N'aspnet_Users'
            RETURN
        END
        IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
        BEGIN
            SELECT N'aspnet_Applications'
            RETURN
        END
    END
END

Procedure: aspnet_Applications_CreateApplication

Description

Adds a new application to the aspnet_Application table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@ApplicationId  uniqueidentifier  Input/Output 

Definition

CREATE PROCEDURE [dbo].aspnet_Applications_CreateApplication
    @ApplicationName      nvarchar(256),
    @ApplicationId        uniqueidentifier OUTPUT
AS
BEGIN
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

    IF(@ApplicationId IS NULL)
    BEGIN
        DECLARE @TranStarted   bit
        SET @TranStarted = 0

        IF( @@TRANCOUNT = 0 )
        BEGIN
	        BEGIN TRANSACTION
	        SET @TranStarted = 1
        END
        ELSE
    	    SET @TranStarted = 0

        SELECT  @ApplicationId = ApplicationId
        FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
        WHERE LOWER(@ApplicationName) = LoweredApplicationName

        IF(@ApplicationId IS NULL)
        BEGIN
            SELECT  @ApplicationId = NEWID()
            INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
            VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
        END


        IF( @TranStarted = 1 )
        BEGIN
            IF(@@ERROR = 0)
            BEGIN
	        SET @TranStarted = 0
	        COMMIT TRANSACTION
            END
            ELSE
            BEGIN
                SET @TranStarted = 0
                ROLLBACK TRANSACTION
            END
        END
    END
END

Procedure: aspnet_CheckSchemaVersion

Description

Checks the compatibility of the schema version for the given feature.

Parameters

Name  Type  Direction 
@Feature  nvarchar  Input 
@CompatibleSchemaVersion  nvarchar  Input 

Definition

CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    IF (EXISTS( SELECT  *
                FROM    dbo.aspnet_SchemaVersions
                WHERE   Feature = LOWER( @Feature ) AND
                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
        RETURN 0

    RETURN 1
END

Procedure: aspnet_Membership_ChangePasswordQuestionAndAnswer

Description

Changes the specified user's password question and answer.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@NewPasswordQuestion  nvarchar  Input 
@NewPasswordAnswer  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
    @ApplicationName       nvarchar(256),
    @UserName              nvarchar(256),
    @NewPasswordQuestion   nvarchar(256),
    @NewPasswordAnswer     nvarchar(128)
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId
    IF (@UserId IS NULL)
    BEGIN
        RETURN(1)
    END

    UPDATE dbo.aspnet_Membership
    SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
    WHERE  UserId=@UserId
    RETURN(0)
END

Procedure: aspnet_Membership_CreateUser

Description

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.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@Password  nvarchar  Input 
@PasswordSalt  nvarchar  Input 
@Email  nvarchar  Input 
@PasswordQuestion  nvarchar  Input 
@PasswordAnswer  nvarchar  Input 
@IsApproved  bit  Input 
@CurrentTimeUtc  datetime  Input 
@CreateDate  datetime  Input 
@UniqueEmail  int  Input 
@PasswordFormat  int  Input 
@UserId  uniqueidentifier  Input/Output 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
    @ApplicationName                        nvarchar(256),
    @UserName                               nvarchar(256),
    @Password                               nvarchar(128),
    @PasswordSalt                           nvarchar(128),
    @Email                                  nvarchar(256),
    @PasswordQuestion                       nvarchar(256),
    @PasswordAnswer                         nvarchar(128),
    @IsApproved                             bit,
    @CurrentTimeUtc                         datetime,
    @CreateDate                             datetime = NULL,
    @UniqueEmail                            int      = 0,
    @PasswordFormat                         int      = 0,
    @UserId                                 uniqueidentifier OUTPUT
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @NewUserId uniqueidentifier
    SELECT @NewUserId = NULL

    DECLARE @IsLockedOut bit
    SET @IsLockedOut = 0

    DECLARE @LastLockoutDate  datetime
    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAttemptCount int
    SET @FailedPasswordAttemptCount = 0

    DECLARE @FailedPasswordAttemptWindowStart  datetime
    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAnswerAttemptCount int
    SET @FailedPasswordAnswerAttemptCount = 0

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @NewUserCreated bit
    DECLARE @ReturnValue   int
    SET @ReturnValue = 0

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    SET @CreateDate = @CurrentTimeUtc

    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
    IF ( @NewUserId IS NULL )
    BEGIN
        SET @NewUserId = @UserId
        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
        SET @NewUserCreated = 1
    END
    ELSE
    BEGIN
        SET @NewUserCreated = 0
        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
        BEGIN
            SET @ErrorCode = 6
            GOTO Cleanup
        END
    END

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @ReturnValue = -1 )
    BEGIN
        SET @ErrorCode = 10
        GOTO Cleanup
    END

    IF ( EXISTS ( SELECT UserId
                  FROM   dbo.aspnet_Membership
                  WHERE  @NewUserId = UserId ) )
    BEGIN
        SET @ErrorCode = 6
        GOTO Cleanup
    END

    SET @UserId = @NewUserId

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            SET @ErrorCode = 7
            GOTO Cleanup
        END
    END

    IF (@NewUserCreated = 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate = @CreateDate
        WHERE  @UserId = UserId
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    INSERT INTO dbo.aspnet_Membership
                ( ApplicationId,
                  UserId,
                  Password,
                  PasswordSalt,
                  Email,
                  LoweredEmail,
                  PasswordQuestion,
                  PasswordAnswer,
                  PasswordFormat,
                  IsApproved,
                  IsLockedOut,
                  CreateDate,
                  LastLoginDate,
                  LastPasswordChangedDate,
                  LastLockoutDate,
                  FailedPasswordAttemptCount,
                  FailedPasswordAttemptWindowStart,
                  FailedPasswordAnswerAttemptCount,
                  FailedPasswordAnswerAttemptWindowStart )
         VALUES ( @ApplicationId,
                  @UserId,
                  @Password,
                  @PasswordSalt,
                  @Email,
                  LOWER(@Email),
                  @PasswordQuestion,
                  @PasswordAnswer,
                  @PasswordFormat,
                  @IsApproved,
                  @IsLockedOut,
                  @CreateDate,
                  @CreateDate,
                  @CreateDate,
                  @LastLockoutDate,
                  @FailedPasswordAttemptCount,
                  @FailedPasswordAttemptWindowStart,
                  @FailedPasswordAnswerAttemptCount,
                  @FailedPasswordAnswerAttemptWindowStart )

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Procedure: aspnet_Membership_FindUsersByEmail

Description

Retrieves records from aspnet_Membership table with email addresses matching the specified pattern and with the specified application ID.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@EmailToMatch  nvarchar  Input 
@PageIndex  int  Input 
@PageSize  int  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
    @ApplicationName       nvarchar(256),
    @EmailToMatch          nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    IF( @EmailToMatch IS NULL )
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT u.UserId
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
            ORDER BY m.LoweredEmail
    ELSE
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT u.UserId
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
            ORDER BY m.LoweredEmail

    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY m.LoweredEmail

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END

Procedure: aspnet_Membership_FindUsersByName

Description

Retrieves records from aspnet_Membership table with user names matching the specified pattern and with the specified application ID.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserNameToMatch  nvarchar  Input 
@PageIndex  int  Input 
@PageSize  int  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByName
    @ApplicationName       nvarchar(256),
    @UserNameToMatch       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT u.UserId
        FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
        ORDER BY u.UserName


    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END

Procedure: aspnet_Membership_GetAllUsers

Description

Retrieves all users from the aspnet_Membership table with the specified application ID.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@PageIndex  int  Input 
@PageSize  int  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers
    @ApplicationName       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0


    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName

    SELECT @TotalRecords = @@ROWCOUNT

    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName
    RETURN @TotalRecords
END

Procedure: aspnet_Membership_GetNumberOfUsersOnline

Description

Gets the number of users currently online (those whose last activity dates.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@MinutesSinceLastInActive  int  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
    @ApplicationName            nvarchar(256),
    @MinutesSinceLastInActive   int,
    @CurrentTimeUtc             datetime
AS
BEGIN
    DECLARE @DateActive datetime
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

    DECLARE @NumOnline int
    SELECT  @NumOnline = COUNT(*)
    FROM    dbo.aspnet_Users u(NOLOCK),
            dbo.aspnet_Applications a(NOLOCK),
            dbo.aspnet_Membership m(NOLOCK)
    WHERE   u.ApplicationId = a.ApplicationId                  AND
            LastActivityDate > @DateActive                     AND
            a.LoweredApplicationName = LOWER(@ApplicationName) AND
            u.UserId = m.UserId
    RETURN(@NumOnline)
END

Procedure: aspnet_Membership_GetPassword

Description

Gets the specified user's password data from the database. Used for retrieving passwords with a user-supplied password answer.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@MaxInvalidPasswordAttempts  int  Input 
@PasswordAttemptWindow  int  Input 
@CurrentTimeUtc  datetime  Input 
@PasswordAnswer  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_GetPassword
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @MaxInvalidPasswordAttempts     int,
    @PasswordAttemptWindow          int,
    @CurrentTimeUtc                 datetime,
    @PasswordAnswer                 nvarchar(128) = NULL
AS
BEGIN
    DECLARE @UserId                                 uniqueidentifier
    DECLARE @PasswordFormat                         int
    DECLARE @Password                               nvarchar(128)
    DECLARE @passAns                                nvarchar(128)
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId,
            @Password = m.Password,
            @passAns = m.PasswordAnswer,
            @PasswordFormat = m.PasswordFormat,
            @IsLockedOut = m.IsLockedOut,
            @LastLockoutDate = m.LastLockoutDate,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF ( @@rowcount = 0 )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    IF( @IsLockedOut = 1 )
    BEGIN
        SET @ErrorCode = 99
        GOTO Cleanup
    END

    IF ( NOT( @PasswordAnswer IS NULL ) )
    BEGIN
        IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
        BEGIN
            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @CurrentTimeUtc
                END
            END

            SET @ErrorCode = 3
        END
        ELSE
        BEGIN
            IF( @FailedPasswordAnswerAttemptCount > 0 )
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = 0
                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            END
        END

        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
        WHERE @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    IF( @ErrorCode = 0 )
        SELECT @Password, @PasswordFormat

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Procedure: aspnet_Membership_GetPasswordWithFormat

Description

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).

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@UpdateLastLoginActivityDate  bit  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @UpdateLastLoginActivityDate    bit,
    @CurrentTimeUtc                 datetime
AS
BEGIN
    DECLARE @IsLockedOut                        bit
    DECLARE @UserId                             uniqueidentifier
    DECLARE @Password                           nvarchar(128)
    DECLARE @PasswordSalt                       nvarchar(128)
    DECLARE @PasswordFormat                     int
    DECLARE @FailedPasswordAttemptCount         int
    DECLARE @FailedPasswordAnswerAttemptCount   int
    DECLARE @IsApproved                         bit
    DECLARE @LastActivityDate                   datetime
    DECLARE @LastLoginDate                      datetime

    SELECT  @UserId          = NULL

    SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
            @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
            @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF (@UserId IS NULL)
        RETURN 1

    IF (@IsLockedOut = 1)
        RETURN 99

    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
             @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate

    IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
    BEGIN
        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @CurrentTimeUtc
        WHERE   UserId = @UserId

        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @CurrentTimeUtc
        WHERE   @UserId = UserId
    END


    RETURN 0
END

Procedure: aspnet_Membership_GetUserByEmail

Description

Given an e-mail address and application ID, retrieves the corresponding record from the aspnet_Membership table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@Email  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_GetUserByEmail
    @ApplicationName  nvarchar(256),
    @Email            nvarchar(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@Email) = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END

Procedure: aspnet_Membership_GetUserByName

Description

Given a user name and application ID, retrieves the corresponding record from the aspnet_Membership table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@CurrentTimeUtc  datetime  Input 
@UpdateLastActivity  bit  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN
        -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1

        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId

        SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut, m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    RETURN 0
END

Procedure: aspnet_Membership_GetUserByUserId

Description

Given a user ID and application ID, retrieves the corresponding record from the aspnet_Membership table.

Parameters

Name  Type  Direction 
@UserId  uniqueidentifier  Input 
@CurrentTimeUtc  datetime  Input 
@UpdateLastActivity  bit  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_GetUserByUserId
    @UserId               uniqueidentifier,
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    IF ( @UpdateLastActivity = 1 )
    BEGIN
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        FROM     dbo.aspnet_Users
        WHERE    @UserId = UserId

        IF ( @@ROWCOUNT = 0 ) -- User ID not found
            RETURN -1
    END

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
            m.LastLockoutDate
    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   @UserId = u.UserId AND u.UserId = m.UserId

    IF ( @@ROWCOUNT = 0 ) -- User ID not found
       RETURN -1

    RETURN 0
END

Procedure: aspnet_Membership_ResetPassword

Description

Resets the specified user's password based on a password answer.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@NewPassword  nvarchar  Input 
@MaxInvalidPasswordAttempts  int  Input 
@PasswordAttemptWindow  int  Input 
@PasswordSalt  nvarchar  Input 
@CurrentTimeUtc  datetime  Input 
@PasswordFormat  int  Input 
@PasswordAnswer  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_ResetPassword
    @ApplicationName             nvarchar(256),
    @UserName                    nvarchar(256),
    @NewPassword                 nvarchar(128),
    @MaxInvalidPasswordAttempts  int,
    @PasswordAttemptWindow       int,
    @PasswordSalt                nvarchar(128),
    @CurrentTimeUtc              datetime,
    @PasswordFormat              int = 0,
    @PasswordAnswer              nvarchar(128) = NULL
AS
BEGIN
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @UserId                                 uniqueidentifier
    SET     @UserId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    SELECT @IsLockedOut = IsLockedOut,
           @LastLockoutDate = LastLockoutDate,
           @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
           @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
           @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
           @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
    FROM dbo.aspnet_Membership WITH ( UPDLOCK )
    WHERE @UserId = UserId

    IF( @IsLockedOut = 1 )
    BEGIN
        SET @ErrorCode = 99
        GOTO Cleanup
    END

    UPDATE dbo.aspnet_Membership
    SET    Password = @NewPassword,
           LastPasswordChangedDate = @CurrentTimeUtc,
           PasswordFormat = @PasswordFormat,
           PasswordSalt = @PasswordSalt
    WHERE  @UserId = UserId AND
           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )

    IF ( @@ROWCOUNT = 0 )
        BEGIN
            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @CurrentTimeUtc
                END
            END

            SET @ErrorCode = 3
        END
    ELSE
        BEGIN
            IF( @FailedPasswordAnswerAttemptCount > 0 )
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = 0
                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            END
        END

    IF( NOT ( @PasswordAnswer IS NULL ) )
    BEGIN
        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
        WHERE @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Procedure: aspnet_Membership_SetPassword

Description

Sets the specified user's password to the password input to the stored procedure.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@NewPassword  nvarchar  Input 
@PasswordSalt  nvarchar  Input 
@CurrentTimeUtc  datetime  Input 
@PasswordFormat  int  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_SetPassword
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @NewPassword      nvarchar(128),
    @PasswordSalt     nvarchar(128),
    @CurrentTimeUtc   datetime,
    @PasswordFormat   int = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF (@UserId IS NULL)
        RETURN(1)

    UPDATE dbo.aspnet_Membership
    SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
        LastPasswordChangedDate = @CurrentTimeUtc
    WHERE @UserId = UserId
    RETURN(0)
END

Procedure: aspnet_Membership_UnlockUser

Description

Restores login privileges for the specified user by setting the user's IsLockedOut bit to 0.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_UnlockUser
    @ApplicationName                         nvarchar(256),
    @UserName                                nvarchar(256)
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
        RETURN 1

    UPDATE dbo.aspnet_Membership
    SET IsLockedOut = 0,
        FailedPasswordAttemptCount = 0,
        FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
        FailedPasswordAnswerAttemptCount = 0,
        FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
        LastLockoutDate = CONVERT( datetime, '17540101', 112 )
    WHERE @UserId = UserId

    RETURN 0
END

Procedure: aspnet_Membership_UpdateUser

Description

Updates the user's last activity date in the aspnet_Users table and e-mail address, comment, isapproved status, and last login date in the aspnet_Membership table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@Email  nvarchar  Input 
@Comment  ntext  Input 
@IsApproved  bit  Input 
@LastLoginDate  datetime  Input 
@LastActivityDate  datetime  Input 
@UniqueEmail  int  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_UpdateUser
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @Email                nvarchar(256),
    @Comment              ntext,
    @IsApproved           bit,
    @LastLoginDate        datetime,
    @LastActivityDate     datetime,
    @UniqueEmail          int,
    @CurrentTimeUtc       datetime
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId, @ApplicationId = a.ApplicationId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF (@UserId IS NULL)
        RETURN(1)

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
                    WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            RETURN(7)
        END
    END

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
	SET @TranStarted = 0

    UPDATE dbo.aspnet_Users WITH (ROWLOCK)
    SET
         LastActivityDate = @LastActivityDate
    WHERE
       @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
    SET
         Email            = @Email,
         LoweredEmail     = LOWER(@Email),
         Comment          = @Comment,
         IsApproved       = @IsApproved,
         LastLoginDate    = @LastLoginDate
    WHERE
       @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN -1
END

Procedure: aspnet_Membership_UpdateUserInfo

Description

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.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@IsPasswordCorrect  bit  Input 
@UpdateLastLoginActivityDate  bit  Input 
@MaxInvalidPasswordAttempts  int  Input 
@PasswordAttemptWindow  int  Input 
@CurrentTimeUtc  datetime  Input 
@LastLoginDate  datetime  Input 
@LastActivityDate  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @IsPasswordCorrect              bit,
    @UpdateLastLoginActivityDate    bit,
    @MaxInvalidPasswordAttempts     int,
    @PasswordAttemptWindow          int,
    @CurrentTimeUtc                 datetime,
    @LastLoginDate                  datetime,
    @LastActivityDate               datetime
AS
BEGIN
    DECLARE @UserId                                 uniqueidentifier
    DECLARE @IsApproved                             bit
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId,
            @IsApproved = m.IsApproved,
            @IsLockedOut = m.IsLockedOut,
            @LastLockoutDate = m.LastLockoutDate,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF ( @@rowcount = 0 )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    IF( @IsLockedOut = 1 )
    BEGIN
        GOTO Cleanup
    END

    IF( @IsPasswordCorrect = 0 )
    BEGIN
        IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
            SET @FailedPasswordAttemptCount = 1
        END
        ELSE
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
            SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
        END

        BEGIN
            IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
            BEGIN
                SET @IsLockedOut = 1
                SET @LastLockoutDate = @CurrentTimeUtc
            END
        END
    END
    ELSE
    BEGIN
        IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
        BEGIN
            SET @FailedPasswordAttemptCount = 0
            SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            SET @FailedPasswordAnswerAttemptCount = 0
            SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
        END
    END

    IF( @UpdateLastLoginActivityDate = 1 )
    BEGIN
        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @LastActivityDate
        WHERE   @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END

        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @LastLoginDate
        WHERE   UserId = @UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END


    UPDATE dbo.aspnet_Membership
    SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
  FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
        FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
        FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
        FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
    WHERE @UserId = UserId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Procedure: aspnet_Paths_CreatePath

Description

Retrieves a path ID from the aspnet_Paths table, or creates a new one if the specified path doesn't exist.

Parameters

Name  Type  Direction 
@ApplicationId  uniqueidentifier  Input 
@Path  nvarchar  Input 
@PathId  uniqueidentifier  Input/Output 

Definition

CREATE PROCEDURE dbo.aspnet_Paths_CreatePath
    @ApplicationId UNIQUEIDENTIFIER,
    @Path           NVARCHAR(256),
    @PathId         UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    BEGIN TRANSACTION
    IF (NOT EXISTS(SELECT * FROM dbo.aspnet_Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
    BEGIN
        INSERT dbo.aspnet_Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
    END
    COMMIT TRANSACTION
    SELECT @PathId = PathId FROM dbo.aspnet_Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
END

Procedure: aspnet_Personalization_GetApplicationId

Description

Converts the application name input to it into an application ID.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@ApplicationId  uniqueidentifier  Input/Output 

Definition

CREATE PROCEDURE dbo.aspnet_Personalization_GetApplicationId (
    @ApplicationName NVARCHAR(256),
    @ApplicationId UNIQUEIDENTIFIER OUT)
AS
BEGIN
    SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
END

Procedure: aspnet_PersonalizationAdministration_DeleteAllState

Description

Deletes all records from aspnet_PersonalizationAllUsers or aspnet_PersonalizationPerUser corresponding to the specified application ID.

Parameters

Name  Type  Direction 
@AllUsersScope  bit  Input 
@ApplicationName  nvarchar  Input 
@Count  int  Input/Output 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_DeleteAllState (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Count int OUT)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        IF (@AllUsersScope = 1)
            DELETE FROM aspnet_PersonalizationAllUsers
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM dbo.aspnet_Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)
        ELSE
            DELETE FROM aspnet_PersonalizationPerUser
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM dbo.aspnet_Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)

        SELECT @Count = @@ROWCOUNT
    END
END

Procedure: aspnet_PersonalizationAdministration_FindState

Description

Retrieves profile data from aspnet_PersonalizationAllUsers or aspnet_PersonalizationPerUser meeting several input criteria.

Parameters

Name  Type  Direction 
@AllUsersScope  bit  Input 
@ApplicationName  nvarchar  Input 
@PageIndex  int  Input 
@PageSize  int  Input 
@Path  nvarchar  Input 
@UserName  nvarchar  Input 
@InactiveSinceDate  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_FindState (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @PageIndex              INT,
    @PageSize               INT,
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        RETURN

    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table to store the selected results
    CREATE TABLE #PageIndex (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ItemId UNIQUEIDENTIFIER
    )

    IF (@AllUsersScope = 1)
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT Paths.PathId
        FROM dbo.aspnet_Paths Paths,
             ((SELECT Paths.PathId
               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND AllUsers.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT DISTINCT Paths.PathId
               FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND PerUser.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path,
               SharedDataPerPath.LastUpdatedDate,
               SharedDataPerPath.SharedDataLength,
               UserDataPerPath.UserDataLength,
               UserDataPerPath.UserCount
        FROM dbo.aspnet_Paths Paths,
             ((SELECT PageIndex.ItemId AS PathId,
                      AllUsers.LastUpdatedDate AS LastUpdatedDate,
                      DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
               WHERE AllUsers.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT PageIndex.ItemId AS PathId,
                      SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
                      COUNT(*) AS UserCount
               FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
               WHERE PerUser.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
               GROUP BY PageIndex.ItemId
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC
    END
    ELSE
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT PerUser.Id
        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
        WHERE Paths.ApplicationId = @ApplicationId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
    AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
              AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
        ORDER BY Paths.Path ASC, Users.UserName ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
        WHERE PerUser.Id = PageIndex.ItemId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
        ORDER BY Paths.Path ASC, Users.UserName ASC
    END

    RETURN @TotalRecords
END

Procedure: aspnet_PersonalizationAdministration_GetCountOfState

Description

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.

Parameters

Name  Type  Direction 
@Count  int  Input/Output 
@AllUsersScope  bit  Input 
@ApplicationName  nvarchar  Input 
@Path  nvarchar  Input 
@UserName  nvarchar  Input 
@InactiveSinceDate  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_GetCountOfState (
    @Count int OUT,
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN

    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
        IF (@AllUsersScope = 1)
            SELECT @Count = COUNT(*)
            FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND AllUsers.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
        ELSE
            SELECT @Count = COUNT(*)
            FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND PerUser.UserId = Users.UserId
                  AND PerUser.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
                  AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
                  AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
END

Procedure: aspnet_PersonalizationAdministration_ResetSharedState

Description

Resets shared state for the specified page, by deleting the corresponding record from the aspnet_PersonalizationAllUsers table.

Parameters

Name  Type  Direction 
@Count  int  Input/Output 
@ApplicationName  nvarchar  Input 
@Path  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetSharedState (
    @Count int OUT,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationAllUsers
        WHERE PathId IN
            (SELECT AllUsers.PathId
             FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
             WHERE Paths.ApplicationId = @ApplicationId
                   AND AllUsers.PathId = Paths.PathId
                   AND Paths.LoweredPath = LOWER(@Path))

        SELECT @Count = @@ROWCOUNT
    END
END

Procedure: aspnet_PersonalizationAdministration_ResetUserState

Description

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.

Parameters

Name  Type  Direction 
@Count  int  Input/Output 
@ApplicationName  nvarchar  Input 
@InactiveSinceDate  datetime  Input 
@UserName  nvarchar  Input 
@Path  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetUserState (
    @Count                  int                 OUT,
    @ApplicationName        NVARCHAR(256),
    @InactiveSinceDate      DATETIME            = NULL,
    @UserName               NVARCHAR(256)       = NULL,
    @Path                   NVARCHAR(256)       = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser
        WHERE Id IN (SELECT PerUser.Id
                     FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
                     WHERE Paths.ApplicationId = @ApplicationId
                           AND PerUser.UserId = Users.UserId
                           AND PerUser.PathId = Paths.PathId
                           AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
                           AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
                           AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))

        SELECT @Count = @@ROWCOUNT
    END
END

Procedure: aspnet_PersonalizationAllUsers_GetPageSettings

Description

Retrieves shared state for the specified page from the aspnet_PersonalizationAllUsers table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@Path  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_GetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationAllUsers p WHERE p.PathId = @PathId
END

Procedure: aspnet_PersonalizationAllUsers_ResetPageSettings

Description

Resets shared state for the specified page, by deleting the corresponding record from the aspnet_PersonalizationAllUsers table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@Path  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_ResetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    DELETE FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId
    RETURN 0
END

Procedure: aspnet_PersonalizationAllUsers_SetPageSettings

Description

Saves shared state for the specified page in the aspnet_PersonalizationAllUsers table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@Path  nvarchar  Input 
@PageSettings  image  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_SetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId))
        UPDATE dbo.aspnet_PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
    ELSE
        INSERT INTO dbo.aspnet_PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END

Procedure: aspnet_PersonalizationPerUser_GetPageSettings

Description

Retrieves per-user state for the specified page and the specified user from the aspnet_PersonalizationPerUser table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@Path  nvarchar  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_GetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
END

Procedure: aspnet_PersonalizationPerUser_ResetPageSettings

Description

Resets per-user state for the specified page and the specified user, by deleting the corresponding record from the aspnet_PersonalizationPerUser table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@Path  nvarchar  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_ResetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
    RETURN 0
END

Procedure: aspnet_PersonalizationPerUser_SetPageSettings

Description

Saves per-user state for the specified page and the specified user in the aspnet_PersonalizationPerUser table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@Path  nvarchar  Input 
@PageSettings  image  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_SetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
        UPDATE dbo.aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
    ELSE
        INSERT INTO dbo.aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END

Procedure: aspnet_Profile_DeleteInactiveProfiles

Description

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.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@ProfileAuthOptions  int  Input 
@InactiveSinceDate  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Profile_DeleteInactiveProfiles
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @InactiveSinceDate      datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT  0
        RETURN
    END

    DELETE
    FROM    dbo.aspnet_Profile
    WHERE   UserId IN
            (   SELECT  UserId
                FROM    dbo.aspnet_Users u
                WHERE   ApplicationId = @ApplicationId
                        AND (LastActivityDate <= @InactiveSinceDate)
                        AND (
                                (@ProfileAuthOptions = 2)
                             OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                             OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                            )
            )

    SELECT  @@ROWCOUNT
END

Procedure: aspnet_Profile_DeleteProfiles

Description

Deletes profile data from the aspnet_Profile table for the specified users.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserNames  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Profile_DeleteProfiles
    @ApplicationName        nvarchar(256),
    @UserNames              nvarchar(4000)
AS
BEGIN
    DECLARE @UserName     nvarchar(256)
    DECLARE @CurrentPos   int
    DECLARE @NextPos      int
    DECLARE @NumDeleted   int
    DECLARE @DeletedUser  int
    DECLARE @TranStarted  bit
    DECLARE @ErrorCode    int

    SET @ErrorCode = 0
    SET @CurrentPos = 1
    SET @NumDeleted = 0
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    WHILE (@CurrentPos <= LEN(@UserNames))
    BEGIN
        SELECT @NextPos = CHARINDEX(N',', @UserNames,  @CurrentPos)
        IF (@NextPos = 0 OR @NextPos IS NULL)
            SELECT @NextPos = LEN(@UserNames) + 1

        SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
        SELECT @CurrentPos = @NextPos+1

        IF (LEN(@UserName) > 0)
        BEGIN
            SELECT @DeletedUser = 0
            EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
            IF (@DeletedUser <> 0)
                SELECT @NumDeleted = @NumDeleted + 1
        END
    END
    SELECT @NumDeleted
    IF (@TranStarted = 1)
    BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
    END
    SET @TranStarted = 0

    RETURN 0

Cleanup:
    IF (@TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    RETURN @ErrorCode
END

Procedure: aspnet_Profile_GetNumberOfInactiveProfiles

Description

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.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@ProfileAuthOptions  int  Input 
@InactiveSinceDate  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @InactiveSinceDate      datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT 0
        RETURN
    END

    SELECT  COUNT(*)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
    WHERE   ApplicationId = @ApplicationId
        AND u.UserId = p.UserId
        AND (LastActivityDate <= @InactiveSinceDate)
        AND (
                (@ProfileAuthOptions = 2)
                OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
            )
END

Procedure: aspnet_Profile_GetProfiles

Description

Retrieves profile data from the aspnet_Profile table for users who match the criteria input to the stored procedure.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@ProfileAuthOptions  int  Input 
@PageIndex  int  Input 
@PageSize  int  Input 
@UserNameToMatch  nvarchar  Input 
@InactiveSinceDate  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Profile_GetProfiles
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @PageIndex              int,
    @PageSize               int,
    @UserNameToMatch        nvarchar(256) = NULL,
    @InactiveSinceDate      datetime      = NULL
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT  u.UserId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
        WHERE   ApplicationId = @ApplicationId
            AND u.UserId = p.UserId
            AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
            AND (     (@ProfileAuthOptions = 2)
                   OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                   OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                 )
            AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
        ORDER BY UserName

    SELECT  u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
            DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
    WHERE   u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound

    SELECT COUNT(*)
    FROM   #PageIndexForUsers

    DROP TABLE #PageIndexForUsers
END

Procedure: aspnet_Profile_GetProperties

Description

Retrieves profile data for the specified user.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Profile_GetProperties
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
    FROM         dbo.aspnet_Profile
    WHERE        UserId = @UserId

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    END
END

Procedure: aspnet_Profile_SetProperties

Description

Saves profile data for the specified user.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@PropertyNames  ntext  Input 
@PropertyValuesString  ntext  Input 
@PropertyValuesBinary  image  Input 
@UserName  nvarchar  Input 
@IsUserAnonymous  bit  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Profile_SetProperties
    @ApplicationName        nvarchar(256),
    @PropertyNames          ntext,
    @PropertyValuesString   ntext,
    @PropertyValuesBinary   image,
    @UserName               nvarchar(256),
    @IsUserAnonymous        bit,
    @CurrentTimeUtc         datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
       BEGIN TRANSACTION
       SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    DECLARE @UserId uniqueidentifier
    DECLARE @LastActivityDate datetime
    SELECT  @UserId = NULL
    SELECT  @LastActivityDate = @CurrentTimeUtc

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    UPDATE dbo.aspnet_Users
    SET    LastActivityDate=@CurrentTimeUtc
    WHERE  UserId = @UserId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS( SELECT *
               FROM   dbo.aspnet_Profile
               WHERE  UserId = @UserId))
        UPDATE dbo.aspnet_Profile
        SET    PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
               PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    ELSE
        INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
             VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Procedure: aspnet_RegisterSchemaVersion

Description

Registers the compatible schema required for the given feature.

Parameters

Name  Type  Direction 
@Feature  nvarchar  Input 
@CompatibleSchemaVersion  nvarchar  Input 
@IsCurrentVersion  bit  Input 
@RemoveIncompatibleSchema  bit  Input 

Definition

CREATE PROCEDURE [dbo].aspnet_RegisterSchemaVersion
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128),
    @IsCurrentVersion          bit,
    @RemoveIncompatibleSchema  bit
AS
BEGIN
    IF( @RemoveIncompatibleSchema = 1 )
    BEGIN
        DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
    END
    ELSE
    BEGIN
        IF( @IsCurrentVersion = 1 )
        BEGIN
            UPDATE dbo.aspnet_SchemaVersions
            SET IsCurrentVersion = 0
            WHERE Feature = LOWER( @Feature )
        END
    END

    INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
    VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
END

Procedure: aspnet_Roles_CreateRole

Description

Adds a role to the aspnet_Roles table and, if necessary, adds a new application to the aspnet_Applications table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@RoleName  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    INSERT INTO dbo.aspnet_Roles
                (ApplicationId, RoleName, LoweredRoleName)
         VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Procedure: aspnet_Roles_DeleteRole

Description

Removes a role from the aspnet_Roles table. Optionally deletes records referencing the deleted role from the aspnet_UsersInRoles table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@RoleName  nvarchar  Input 
@DeleteOnlyIfRoleIsEmpty  bit  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Roles_DeleteRole
    @ApplicationName            nvarchar(256),
    @RoleName                   nvarchar(256),
    @DeleteOnlyIfRoleIsEmpty    bit
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    DECLARE @RoleId   uniqueidentifier
    SELECT  @RoleId = NULL
    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
    BEGIN
        SELECT @ErrorCode = 1
        GOTO Cleanup
    END
    IF (@DeleteOnlyIfRoleIsEmpty <> 0)
    BEGIN
        IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId))
        BEGIN
            SELECT @ErrorCode = 2
            GOTO Cleanup
        END
    END


    DELETE FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId  AND ApplicationId = @ApplicationId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END

Procedure: aspnet_Roles_GetAllRoles

Description

Retrieves all roles with the specified application ID from the aspnet_Roles table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Roles_GetAllRoles (
    @ApplicationName           nvarchar(256))
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN
    SELECT RoleName
    FROM   dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
    ORDER BY RoleName
END

Procedure: aspnet_Roles_RoleExists

Description

Checks the aspnet_Roles table to determine whether the specified role exists.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@RoleName  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_Roles_RoleExists
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(0)
    IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
        RETURN(1)
    ELSE
        RETURN(0)
END

Procedure: aspnet_Setup_RemoveAllRoleMembers

Description

Removes all roles from the given SQL account.

Parameters

Name  Type  Direction 
@name  nvarchar  Input 

Definition

CREATE PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
    @name   sysname
AS
BEGIN
    CREATE TABLE #aspnet_RoleMembers
    (
        Group_name      sysname,
        Group_id        smallint,
        Users_in_group  sysname,
        User_id         smallint
    )

    INSERT INTO #aspnet_RoleMembers
    EXEC sp_helpuser @name

    DECLARE @user_id smallint
    DECLARE @cmd nvarchar(500)
    DECLARE c1 cursor FORWARD_ONLY FOR
        SELECT User_id FROM #aspnet_RoleMembers

    OPEN c1

    FETCH c1 INTO @user_id
    WHILE (@@fetch_status = 0)
    BEGIN
        SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
        EXEC (@cmd)
        FETCH c1 INTO @user_id
    END

    CLOSE c1
    DEALLOCATE c1
END

Procedure: aspnet_Setup_RestorePermissions

Description

Restores permissions to the given SQL account.

Parameters

Name  Type  Direction 
@name  nvarchar  Input 

Definition

CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions
    @name   sysname
AS
BEGIN
    DECLARE @object sysname
    DECLARE @protectType char(10)
    DECLARE @action varchar(60)
    DECLARE @grantee sysname
    DECLARE @cmd nvarchar(500)
    DECLARE c1 cursor FORWARD_ONLY FOR
        SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name

    OPEN c1

    FETCH c1 INTO @object, @protectType, @action, @grantee
    WHILE (@@fetch_status = 0)
    BEGIN
        SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
        EXEC (@cmd)
        FETCH c1 INTO @object, @protectType, @action, @grantee
    END

    CLOSE c1
    DEALLOCATE c1
END

Procedure: aspnet_UnRegisterSchemaVersion

Description

Unregisters the schema version for the given feature.

Parameters

Name  Type  Direction 
@Feature  nvarchar  Input 
@CompatibleSchemaVersion  nvarchar  Input 

Definition

CREATE PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    DELETE FROM dbo.aspnet_SchemaVersions
        WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
END

Procedure: aspnet_Users_CreateUser

Description

Adds a user to the aspnet_Users table. Called by aspnet_Membership_CreateUser.

Parameters

Name  Type  Direction 
@ApplicationId  uniqueidentifier  Input 
@UserName  nvarchar  Input 
@IsUserAnonymous  bit  Input 
@LastActivityDate  datetime  Input 
@UserId  uniqueidentifier  Input/Output 

Definition

CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
    @ApplicationId    uniqueidentifier,
    @UserName         nvarchar(256),
    @IsUserAnonymous  bit,
    @LastActivityDate DATETIME,
    @UserId           uniqueidentifier OUTPUT
AS
BEGIN
    IF( @UserId IS NULL )
        SELECT @UserId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
                    WHERE @UserId = UserId ) )
            RETURN -1
    END

    INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
    VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)

    RETURN 0
END

Procedure: aspnet_Users_DeleteUser

Description

Deletes a user from the aspnet_Membership table and optionally from other SQL provider tables, including aspnet_Users.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@TablesToDeleteFrom  int  Input 
@NumTablesDeletedFrom  int  Input/Output 

Definition

CREATE PROCEDURE [dbo].aspnet_Users_DeleteUser
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @TablesToDeleteFrom int,
    @NumTablesDeletedFrom int OUTPUT
AS
BEGIN
    DECLARE @UserId               uniqueidentifier
    SELECT  @UserId               = NULL
    SELECT  @NumTablesDeletedFrom = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
	SET @TranStarted = 0

    DECLARE @ErrorCode   int
    DECLARE @RowCount    int

    SET @ErrorCode = 0
    SET @RowCount  = 0

    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a
    WHERE   u.LoweredUserName       = LOWER(@UserName)
        AND u.ApplicationId         = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@UserId IS NULL)
    BEGIN
        GOTO Cleanup
    END

    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
               @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
    IF ((@TablesToDeleteFrom & 2) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
    IF ((@TablesToDeleteFrom & 4) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
    IF ((@TablesToDeleteFrom & 8) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (@TablesToDeleteFrom & 2) <> 0 AND
        (@TablesToDeleteFrom & 4) <> 0 AND
        (@TablesToDeleteFrom & 8) <> 0 AND
        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
    BEGIN
        DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:
    SET @NumTablesDeletedFrom = 0

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
	    ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Procedure: aspnet_UsersInRoles_AddUsersToRoles

Description

Adds the specified users to the specified roles by adding them to the aspnet_UsersInRoles table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserNames  nvarchar  Input 
@RoleNames  nvarchar  Input 
@CurrentTimeUtc  datetime  Input 

Definition

CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000),
	@CurrentTimeUtc   datetime
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)
	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num		int
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Name		nvarchar(256)

	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		SELECT TOP 1 Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END

	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1

	WHILE(@Pos <= LEN(@UserNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UserNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		DELETE FROM @tbNames
		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)

		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
		  FROM   @tbNames

		INSERT INTO @tbUsers
		  SELECT  UserId
		  FROM	dbo.aspnet_Users au, @tbNames t
		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
	END

	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
	BEGIN
		SELECT TOP 1 UserName, RoleName
		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
	SELECT UserId, RoleId
	FROM @tbUsers, @tbRoles

	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END

Procedure: aspnet_UsersInRoles_FindUsersInRole

Description

Queries the aspnet_UsersInRoles table for all users belonging to the specified role whose user names match the specified pattern.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@RoleName  nvarchar  Input 
@UserNameToMatch  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256),
    @UserNameToMatch  nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.UserName
    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
    ORDER BY u.UserName
    RETURN(0)
END

Procedure: aspnet_UsersInRoles_GetRolesForUser

Description

Queries the aspnet_UsersInRoles table for all roles assigned to a specified user.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(1)

    SELECT r.RoleName
    FROM   dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
    WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
    ORDER BY r.RoleName
    RETURN (0)
END

Procedure: aspnet_UsersInRoles_GetUsersInRoles

Description

Queries the aspnet_UsersInRoles table for all users belonging to the specified role.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@RoleName  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.UserName
    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
    ORDER BY u.UserName
    RETURN(0)
END

Procedure: aspnet_UsersInRoles_IsUserInRole

Description

Checks the aspnet_UsersInRoles table to determine whether the specified user belongs to the specified role.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserName  nvarchar  Input 
@RoleName  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(2)
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    DECLARE @RoleId uniqueidentifier
    SELECT  @RoleId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(2)

    SELECT  @RoleId = RoleId
    FROM    dbo.aspnet_Roles
    WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
        RETURN(3)

    IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
        RETURN(1)
    ELSE
        RETURN(0)
END

Procedure: aspnet_UsersInRoles_RemoveUsersFromRoles

Description

Removes the specified users from the specified roles by deleting the corresponding records from the aspnet_UsersInRoles table.

Parameters

Name  Type  Direction 
@ApplicationName  nvarchar  Input 
@UserNames  nvarchar  Input 
@RoleNames  nvarchar  Input 

Definition

CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000)
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)


	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers  table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num	  int
	DECLARE @Pos	  int
	DECLARE @NextPos  int
	DECLARE @Name	  nvarchar(256)
	DECLARE @CountAll int
	DECLARE @CountU	  int
	DECLARE @CountR	  int


	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
	SELECT @CountR = @@ROWCOUNT

	IF (@CountR <> @Num)
	BEGIN
		SELECT TOP 1 N'', Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END


	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1


	WHILE(@Pos <= LEN(@UserNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UserNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

	SELECT @CountU = @@ROWCOUNT
	IF (@CountU <> @Num)
	BEGIN
		SELECT TOP 1 Name, N''
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(1)
	END

	SELECT  @CountAll = COUNT(*)
	FROM	dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
	WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId

	IF (@CountAll <> @CountU * @CountR)
	BEGIN
		SELECT TOP 1 UserName, RoleName
		FROM		 @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
		WHERE		 u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
					 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	DELETE FROM dbo.aspnet_UsersInRoles
	WHERE UserId IN (SELECT UserId FROM @tbUsers)
	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END

Procedure: aspnet_WebEvent_LogEvent

Description

Records a Web event in the aspnet_WebEvents_Events table.

Parameters

Name  Type  Direction 
@EventId  char  Input 
@EventTimeUtc  datetime  Input 
@EventTime  datetime  Input 
@EventType  nvarchar  Input 
@EventSequence  decimal  Input 
@EventOccurrence  decimal  Input 
@EventCode  int  Input 
@EventDetailCode  int  Input 
@Message  nvarchar  Input 
@ApplicationPath  nvarchar  Input 
@ApplicationVirtualPath  nvarchar  Input 
@MachineName  nvarchar  Input 
@RequestUrl  nvarchar  Input 
@ExceptionType  nvarchar  Input 
@Details  ntext  Input 

Definition

CREATE PROCEDURE dbo.aspnet_WebEvent_LogEvent
        @EventId         char(32),
        @EventTimeUtc    datetime,
        @EventTime       datetime,
        @EventType       nvarchar(256),
        @EventSequence   decimal(19,0),
        @EventOccurrence decimal(19,0),
        @EventCode       int,
        @EventDetailCode int,
        @Message         nvarchar(1024),
        @ApplicationPath nvarchar(256),
        @ApplicationVirtualPath nvarchar(256),
        @MachineName    nvarchar(256),
        @RequestUrl      nvarchar(1024),
        @ExceptionType   nvarchar(256),
        @Details         ntext
AS
BEGIN
    INSERT
        dbo.aspnet_WebEvent_Events
        (
            EventId,
            EventTimeUtc,
            EventTime,
            EventType,
            EventSequence,
            EventOccurrence,
            EventCode,
            EventDetailCode,
            Message,
            ApplicationPath,
            ApplicationVirtualPath,
            MachineName,
            RequestUrl,
            ExceptionType,
            Details
        )
    VALUES
    (
        @EventId,
        @EventTimeUtc,
        @EventTime,
        @EventType,
        @EventSequence,
        @EventOccurrence,
        @EventCode,
        @EventDetailCode,
        @Message,
        @ApplicationPath,
        @ApplicationVirtualPath,
        @MachineName,
        @RequestUrl,
        @ExceptionType,
        @Details
    )
END

ASP.NET 2.0 Provider Database