.NET Core 3.1 Identity Database Script to Build Necessary DB Objects Without Using Migrate

Not everyone has the database access they need to use the whiz-bang database-migration tools when writing Razor apps with .NET Core 3.1. If this is you, and you either can't get the DBAs to give you that access, or you don't want to be running migrations on a database with other important data in it, then you can use this script directly in SQL Server to build the DB objects you'll need for the default .NET Core 3.1 Identity implementation.

      CREATE TABLE [AspNetRoles] (

          [Id] nvarchar(450) NOT NULL,

          [Name] nvarchar(256) NULL,

          [NormalizedName] nvarchar(256) NULL,

          [ConcurrencyStamp] nvarchar(max) NULL,

          CONSTRAINT [PK_AspNetRoles] PRIMARY KEY ([Id])

      );

      CREATE TABLE [AspNetUsers] (

          [Id] nvarchar(450) NOT NULL,

          [UserName] nvarchar(256) NULL,

          [NormalizedUserName] nvarchar(256) NULL,

          [Email] nvarchar(256) NULL,

          [NormalizedEmail] nvarchar(256) NULL,

          [EmailConfirmed] bit NOT NULL,

          [PasswordHash] nvarchar(max) NULL,

          [SecurityStamp] nvarchar(max) NULL,

          [ConcurrencyStamp] nvarchar(max) NULL,

          [PhoneNumber] nvarchar(max) NULL,

          [PhoneNumberConfirmed] bit NOT NULL,

          [TwoFactorEnabled] bit NOT NULL,

          [LockoutEnd] datetimeoffset NULL,

          [LockoutEnabled] bit NOT NULL,

          [AccessFailedCount] int NOT NULL,

          CONSTRAINT [PK_AspNetUsers] PRIMARY KEY ([Id])

      );

      CREATE TABLE [AspNetRoleClaims] (

          [Id] int NOT NULL IDENTITY,

          [RoleId] nvarchar(450) NOT NULL,

          [ClaimType] nvarchar(max) NULL,

          [ClaimValue] nvarchar(max) NULL,

          CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY ([Id]),

          CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [AspNetRoles] ([Id]) ON DELETE CASCADE

      );

      CREATE TABLE [AspNetUserClaims] (

          [Id] int NOT NULL IDENTITY,

          [UserId] nvarchar(450) NOT NULL,

          [ClaimType] nvarchar(max) NULL,

          [ClaimValue] nvarchar(max) NULL,

          CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY ([Id]),

          CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE

      );

      CREATE TABLE [AspNetUserLogins] (

          [LoginProvider] nvarchar(128) NOT NULL,

          [ProviderKey] nvarchar(128) NOT NULL,

          [ProviderDisplayName] nvarchar(max) NULL,

          [UserId] nvarchar(450) NOT NULL,

          CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY ([LoginProvider], [ProviderKey]),

          CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE

      );

      CREATE TABLE [AspNetUserRoles] (

          [UserId] nvarchar(450) NOT NULL,

          [RoleId] nvarchar(450) NOT NULL,

          CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY ([UserId], [RoleId]),

          CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [AspNetRoles] ([Id]) ON DELETE CASCADE,

          CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE

      );

      CREATE TABLE [AspNetUserTokens] (

          [UserId] nvarchar(450) NOT NULL,

          [LoginProvider] nvarchar(128) NOT NULL,

          [Name] nvarchar(128) NOT NULL,

          [Value] nvarchar(max) NULL,

          CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY ([UserId], [LoginProvider], [Name]),

          CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE

      );

      CREATE INDEX [IX_AspNetRoleClaims_RoleId] ON [AspNetRoleClaims] ([RoleId]);

      CREATE UNIQUE INDEX [RoleNameIndex] ON [AspNetRoles] ([NormalizedName]) WHERE [NormalizedName] IS NOT NULL;

      CREATE INDEX [IX_AspNetUserClaims_UserId] ON [AspNetUserClaims] ([UserId]);

      CREATE INDEX [IX_AspNetUserLogins_UserId] ON [AspNetUserLogins] ([UserId]);

      CREATE INDEX [IX_AspNetUserRoles_RoleId] ON [AspNetUserRoles] ([RoleId]);

      CREATE INDEX [EmailIndex] ON [AspNetUsers] ([NormalizedEmail]);

      CREATE UNIQUE INDEX [UserNameIndex] ON [AspNetUsers] ([NormalizedUserName]) WHERE [NormalizedUserName] IS NOT NULL;