Introduction

In the provided SQL script, the data entry order for the tables is as follows:

  1. aspnet_Membership: Records are inserted first into this table.
  2. aspnet_Users: Records are inserted after inserting records into the aspnet_Membership table.
  3. aspnet_UsersInRoles: Records are inserted last, after inserting records into both the aspnet_Membership and aspnet_Users tables.

This order ensures that any foreign key constraints between these tables are respected, as records in child tables (aspnet_Users and aspnet_UsersInRoles) reference records in the parent table (aspnet_Membership).

In a typical ASP.NET Membership schema, the aspnet_Membership, aspnet_Users, and aspnet_UsersInRoles tables share the UserId column as a common key. Here’s a brief description of the relationships:

  1. aspnet_Users: This table contains user information and has a primary key UserId.
  2. aspnet_Membership: This table contains membership-specific information for users and has a foreign key UserId referencing the aspnet_Users table.
  3. aspnet_UsersInRoles: This table maps users to roles and has a foreign key UserId referencing the aspnet_Users table.

ASP.NET Membership Schema Overview

The ASP.NET Membership schema provides a framework for managing user authentication and authorization in an ASP.NET application. Here’s a brief overview of the key tables involved:

  1. aspnet_Users: Stores basic user information.
  2. aspnet_Membership: Stores membership-specific details, linked to the aspnet_Users table via UserId.
  3. aspnet_UsersInRoles: Maps users to roles, linked to the aspnet_Users table via UserId.

Table Details

1. aspnet_Users

  • UserId (uniqueidentifier, Primary Key): Unique identifier for each user.
  • ApplicationId (uniqueidentifier): Identifier for the application to which the user belongs.
  • UserName (nvarchar): User’s username.
  • LoweredUserName (nvarchar): Lowercase version of the username for case-insensitive searches.
  • MobileAlias (nvarchar): Optional mobile alias.
  • IsAnonymous (bit): Indicates if the user is anonymous.
  • LastActivityDate (datetime): The last time the user was active.

2. aspnet_Membership

  • UserId (uniqueidentifier, Primary Key, Foreign Key): References aspnet_Users.UserId.
  • ApplicationId (uniqueidentifier): Identifier for the application to which the membership belongs.
  • Password (nvarchar): Encrypted user password.
  • PasswordFormat (int): Format of the password (e.g., hashed, encrypted).
  • PasswordSalt (nvarchar): Salt used for hashing the password.
  • Email (nvarchar): User’s email address.
  • PasswordQuestion (nvarchar): Security question for password recovery.
  • PasswordAnswer (nvarchar): Answer to the security question.
  • IsApproved (bit): Indicates if the user is approved.
  • IsLockedOut (bit): Indicates if the user is locked out.
  • CreateDate (datetime): The date the membership was created.
  • LastLoginDate (datetime): The last time the user logged in.
  • LastPasswordChangedDate (datetime): The last time the password was changed.
  • LastLockoutDate (datetime): The last time the user was locked out.
  • FailedPasswordAttemptCount (int): Count of failed password attempts.
  • FailedPasswordAttemptWindowStart (datetime): Start of the period for counting failed password attempts.
  • FailedPasswordAnswerAttemptCount (int): Count of failed attempts to answer the password question.
  • FailedPasswordAnswerAttemptWindowStart (datetime): Start of the period for counting failed password answer attempts.
  • Comment (nvarchar): Additional comments about the membership.

3. aspnet_UsersInRoles

  • UserId (uniqueidentifier, Foreign Key): References aspnet_Users.UserId.
  • RoleId (uniqueidentifier): Identifier for the role.
Add n records to aspnet Users tables
Add n records to aspnet Users tables

Example: add n records to aspnet Users tables

-- Inserting 4000 records into aspnet_Membership, aspnet_Users, and aspnet_UsersInRoles tables

-- Inserting records into aspnet_Membership table
DECLARE @counter INT = 1;
WHILE @counter <= 4000
BEGIN
    INSERT INTO aspnet_Membership (UserId, ApplicationId, Password, PasswordFormat, PasswordSalt, Email, PasswordQuestion, PasswordAnswer, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Comment)
    VALUES ('UserID_' + CAST(@counter AS VARCHAR), 'ApplicationID_' + CAST(@counter AS VARCHAR), 'Password_' + CAST(@counter AS VARCHAR), 1, 'Salt_' + CAST(@counter AS VARCHAR), 'email_' + CAST(@counter AS VARCHAR) + '@example.com', 'Question_' + CAST(@counter AS VARCHAR), 'Answer_' + CAST(@counter AS VARCHAR), 1, 0, GETDATE(), GETDATE(), GETDATE(), GETDATE(), 0, GETDATE(), 0, GETDATE(), 'Comment_' + CAST(@counter AS VARCHAR));
    SET @counter = @counter + 1;
END;

-- Inserting records into aspnet_Users table
SET @counter = 1;
WHILE @counter <= 4000
BEGIN
    INSERT INTO aspnet_Users (UserId, ApplicationId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate)
    VALUES ('UserID_' + CAST(@counter AS VARCHAR), 'ApplicationID_' + CAST(@counter AS VARCHAR), 'UserName_' + CAST(@counter AS VARCHAR), LOWER('UserName_' + CAST(@counter AS VARCHAR)), 'MobileAlias_' + CAST(@counter AS VARCHAR), 0, GETDATE());
    SET @counter = @counter + 1;
END;

If SQL script considering UserId and ApplicationId are of type uniqueidentifier use NEWID() for generate uniqueidentifier

How to add n records to aspnet Users tables: Comprehensive Guide
Tagged on: