ASP.NET MVC 5 + ASP.NET Identity Framework 2.0 – SQL Statement for Creating an ASP.NET Identity Database

ASP.NET MVC 5 + ASP.NET Identity Framework 2.0 – SQL Statement for Creating an ASP.NET Identity Database
 
1. Create the Database From SQL Server First & Execute the Following SQL Statement to Create Table & Foreign Key Constraint.

 
   CREATE TABLE [dbo].[__MigrationHistory] (
      [MigrationId] [nvarchar](150) NOT NULL,
      [ContextKey] [nvarchar](300) NOT NULL,
      [Model] [varbinary](max) NOT NULL,
      [ProductVersion] [nvarchar](32) NOT NULL,
   CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED
   (
      [MigrationId] ASC,
      [ContextKey] ASC
   ) WITH (
      PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
 
   CREATE TABLE [dbo].[AspNetUsers] (
      [Id] [nvarchar](128) NOT NULL,
      [Hometown] [nvarchar](max) NULL,
      [Email] [nvarchar](256) NULL,
      [EmailConfirmed] [bit] NOT NULL,
      [PasswordHash] [nvarchar](max) NULL,
      [SecurityStamp] [nvarchar](max) NULL,
      [PhoneNumber] [nvarchar](max) NULL,
      [PhoneNumberConfirmed] [bit] NOT NULL,
      [TwoFactorEnabled] [bit] NOT NULL,
      [LockoutEndDateUtc] [datetime] NULL,
      [LockoutEnabled] [bit] NOT NULL,
      [AccessFailedCount] [int] NOT NULL,
      [UserName] [nvarchar](256) NOT NULL,
   CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED
   (
      [Id] ASC
   ) WITH (
      PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
 
   CREATE TABLE [dbo].[AspNetUserClaims](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [UserId] [nvarchar](128) NOT NULL,
      [ClaimType] [nvarchar](max) NULL,
      [ClaimValue] [nvarchar](max) NULL,
   CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED
   (
      [Id] ASC
   ) WITH (
      PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserClaims] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]
   FOREIGN KEY ([UserId])
   REFERENCES [dbo].[AspNetUsers] ([Id])
   ON DELETE CASCADE
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]
 
 
   CREATE TABLE [dbo].[AspNetRoles](
      [Id] [nvarchar](128) NOT NULL,
      [Name] [nvarchar](256) NOT NULL,
   CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED
   (
      [Id] ASC
   ) WITH (
      PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
   ) ON [PRIMARY]
 
 
   CREATE TABLE [dbo].[AspNetUserLogins](
      [LoginProvider] [nvarchar](128) NOT NULL,
      [ProviderKey] [nvarchar](128) NOT NULL,
      [UserId] [nvarchar](128) NOT NULL,
   CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED
   (
      [LoginProvider] ASC,
      [ProviderKey] ASC,
      [UserId] ASC
   ) WITH (
      PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
   ) ON [PRIMARY]
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserLogins] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
   FOREIGN KEY ([UserId])
   REFERENCES [dbo].[AspNetUsers] ([Id])
   ON DELETE CASCADE
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
 
 
   CREATE TABLE [dbo].[AspNetUserRoles](
      [UserId] [nvarchar](128) NOT NULL,
      [RoleId] [nvarchar](128) NOT NULL,
   CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED
   (
      [UserId] ASC,
      [RoleId] ASC
   ) WITH (
      PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
   ) ON [PRIMARY]
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
   FOREIGN KEY ([RoleId])
   REFERENCES [dbo].[AspNetRoles] ([Id])
   ON DELETE CASCADE
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
   FOREIGN KEY ([UserId])
   REFERENCES [dbo].[AspNetUsers] ([Id])
   ON DELETE CASCADE
 
   GO
 
   ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
 

2. Change the Connection String on web.conf for connecting to SQL Server Database with ASP.NET Identity Table Structure.
    The Default Predefined Connection String is connected to the SQL Database File ( .MDF ) under the ASP.NET Project.
 
Remark: This Practice is only suitable for the Web Application with User Name & Password Authentication only.
             It is as similar as ASP.NET Membership.
             It is not suitable for the Intranet Website with Windows Authentication Purpose.
 
Reference From : Setting Up ASP.NET Identity Framework 2.0 with Database First (VS2013 Update 2 SPA Template)