ASP.NET MVC 5 Identity, Users, Roles, Accounts, SQL to Generate Tables

In ASP.NET MVC 5, user management changed significantly from ASP.NET MVC 4.

This is the start of a series of blog posts on the topic (long overdue).

This post is on the SQL that your website executes to initially create the identity (user, role) tables.

The SQL the ApplicationUserManager and ApplicationSigninManager generate and execute is below:



CREATE TABLE [dbo].[AspNetRoles] (
    [Id] [nvarchar](128) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY ([Id])
)
go
CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] [nvarchar](128) NOT NULL,
    [RoleId] [nvarchar](128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY ([UserId], [RoleId])
)
go
CREATE TABLE [dbo].[AspNetUsers] (
    [Id] [nvarchar](128) NOT NULL,
    [Email] [nvarchar](256),
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max),
    [SecurityStamp] [nvarchar](max),
    [PhoneNumber] [nvarchar](max),
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEndDateUtc] [datetime],
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY ([Id])
)
go
CREATE TABLE [dbo].[AspNetUserClaims] (
    [Id] [int] NOT NULL IDENTITY,
    [UserId] [nvarchar](128) NOT NULL,
    [ClaimType] [nvarchar](max),
    [ClaimValue] [nvarchar](max),
    CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY ([Id])
)
go
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 ([LoginProvider], [ProviderKey], [UserId])
)
go
CREATE UNIQUE INDEX [RoleNameIndex] ON [dbo].[AspNetRoles]([Name])
go
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]([UserId])
go
CREATE INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]([RoleId])
go
CREATE UNIQUE INDEX [UserNameIndex] ON [dbo].[AspNetUsers]([UserName])
go
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserClaims]([UserId])
go
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]([UserId])
go
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE
go
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
go
ALTER TABLE [dbo].[AspNetUserClaims] ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
go
ALTER TABLE [dbo].[AspNetUserLogins] ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
go


The code for this series can also be found here: https://github.com/aaronhoffman/AspNetMvc5Identity


Hope this helps,
Aaron

Comments

Post a Comment

Popular posts from this blog

Search iPhone Text Messages with SQLite SQL Query

Image
While you're here, check out some of our interactive visualizations: Would you like to visualize your text messages? ------------------------------------------------------------------------------- In my experience, the iPhone text message search functionality is usually pretty awful. Especially if you're trying to find a text from a few years ago. Luckily, if you backup your iPhone using iTunes, your text messages are exported/stored in a SQLite database, and it is fairly easy to query. Here are the steps to SQL Query your Text Messages.   1. Backup your iPhone using iTunes.   2. Find the SQLite file that contains your text messages .     - in ~/Library/Application Support/MobileSync/Backup/* with a filename of 3d0d7e5fb2ce288813306e4d4636395e047a3d28   3. Ensure you have a SQLite Query tool ( SQLiteBrowser is pretty good ).   4. Open your SQLite Text Message DB File using your favorite SQLite query tool.   5. Execute t...
Read more

Configure SonarAnalyzer.CSharp with .editorconfig, no need for SonarCloud or SonarQube

Our goal was to use  SonarSource / SonarQube  static code analysis with the most "minimal" install/configuration footprint. We wanted the static code analysis to break/fail the build on the local developer machines as well as our CI/CD Azure DevOps environment if a rule was violated. SonarSource products usually rely on some external source,  SonarCloud /SonarQube to determine which rules to apply on the local dev machine, and to store the results. We wanted to eliminate these dependencies. SonarSource also provides a Visual Studio extension called  SonarLint  to help check the static code analysis rules within the Visual Studio IDE without needing an external source for the rules. The static code analysis rules SonarLint enforces are also defined in the  SonarAnalyzer.CSharp  nuget package. This nuget package is a set of  Roslyn Code Analyzers . In .NET Core, Roslyn Code Analyzers are triggered during a build if the nuget package is referenced by...
Read more

Edit Default Visual Studio 2012 Item and Project Templates

After adding a new file or project within a Visual Studio solution there are certain settings that I always update.  Follow the steps below to edit the default templates so these settings become the default. Visual Studio 2012 Project and Item Template files are located here: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ItemTemplates C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ProjectTemplates Add "public" to all new C# class files: Edit the Class.cs file by adding the word "public" in front of the word "class" The Class.cs can be found here: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ItemTemplates\CSharp\Code\1033\Class\Class.cs After you're done, it should look like this: using System; using System.Collections.Generic; $if$ ($targetframeworkversion$ >= 3.5)using System.Linq; $endif$using System.Text; $if$ ($targetframeworkversion$ >= 4.5)using System.Threading.Tasks; $e...
Read more