Skip to content

Instantly share code, notes, and snippets.

@jeroenheijmans

jeroenheijmans/AspNetIdentity-SQL-Scripts.sql

Created January 17, 2018 22:33
Show Gist options
  • Save jeroenheijmans/8fa79427abc25a864cb055616644172f to your computer and use it in GitHub Desktop.
Save jeroenheijmans/8fa79427abc25a864cb055616644172f to your computer and use it in GitHub Desktop.
Download ZIP
SQL DDL scripts for [IdentityServer4 + AspNetIdentity] setup
Raw
AspNetIdentity-SQL-Scripts.sql
/*
SQL DDL scripts generated using
dotnet ef database update
for the IdentityServer4.Samples 6_AspNetIdentity quick start. This was run for
commit 0a7400a2ade8c149b3feb08a05bf4423d19ac08c in that repository. It references
- IdentityServer4.AspNetIdentity 2.0.0 including
- Microsoft.AspNetCore.Identity (>= 2.0.1)
- IdentityServer4 (>= 2.1.1)
- Micrsoft.AspNetCore.All including (amongst others)
- Microsoft.EntityFrameworkCore.SqlServer (>= 2.0.1)
See: https://github.com/IdentityServer/IdentityServer4.Samples/tree/release/Quickstarts/6_AspNetIdentity
*/
GO
CREATE TABLE [dbo].[AspNetUsers] (
[Id] NVARCHAR (450) NOT NULL,
[AccessFailedCount] INT NOT NULL,
[ConcurrencyStamp] NVARCHAR (MAX) NULL,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[LockoutEnabled] BIT NOT NULL,
[LockoutEnd] DATETIMEOFFSET (7) NULL,
[NormalizedEmail] NVARCHAR (256) NULL,
[NormalizedUserName] NVARCHAR (256) NULL,
[PasswordHash] NVARCHAR (MAX) NULL,
[PhoneNumber] NVARCHAR (MAX) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[SecurityStamp] NVARCHAR (MAX) NULL,
[TwoFactorEnabled] BIT NOT NULL,
[UserName] NVARCHAR (256) NULL,
CONSTRAINT [PK_AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE NONCLUSTERED INDEX [EmailIndex]
ON [dbo].[AspNetUsers]([NormalizedEmail] ASC);
GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
ON [dbo].[AspNetUsers]([NormalizedUserName] ASC);
GO
CREATE TABLE [dbo].[AspNetUserTokens] (
[UserId] NVARCHAR (450) NOT NULL,
[LoginProvider] NVARCHAR (450) NOT NULL,
[Name] NVARCHAR (450) NOT NULL,
[Value] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [Name] ASC)
);
GO
CREATE TABLE [dbo].[AspNetRoles] (
[Id] NVARCHAR (450) NOT NULL,
[ConcurrencyStamp] NVARCHAR (MAX) NULL,
[Name] NVARCHAR (256) NULL,
[NormalizedName] NVARCHAR (256) NULL,
CONSTRAINT [PK_AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE NONCLUSTERED INDEX [RoleNameIndex]
ON [dbo].[AspNetRoles]([NormalizedName] ASC);
GO
CREATE TABLE [dbo].[AspNetUserRoles] (
[UserId] NVARCHAR (450) NOT NULL,
[RoleId] NVARCHAR (450) NOT NULL,
CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_RoleId]
ON [dbo].[AspNetUserRoles]([RoleId] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_UserId]
ON [dbo].[AspNetUserRoles]([UserId] ASC);
GO
CREATE TABLE [dbo].[AspNetUserLogins] (
[LoginProvider] NVARCHAR (450) NOT NULL,
[ProviderKey] NVARCHAR (450) NOT NULL,
[ProviderDisplayName] NVARCHAR (MAX) NULL,
[UserId] NVARCHAR (450) NOT NULL,
CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC),
CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserLogins_UserId]
ON [dbo].[AspNetUserLogins]([UserId] ASC);
GO
CREATE TABLE [dbo].[AspNetUserClaims] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[ClaimType] NVARCHAR (MAX) NULL,
[ClaimValue] NVARCHAR (MAX) NULL,
[UserId] NVARCHAR (450) NOT NULL,
CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserClaims_UserId]
ON [dbo].[AspNetUserClaims]([UserId] ASC);
GO
CREATE TABLE [dbo].[AspNetRoleClaims] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[ClaimType] NVARCHAR (MAX) NULL,
[ClaimValue] NVARCHAR (MAX) NULL,
[RoleId] NVARCHAR (450) NOT NULL,
CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId]
ON [dbo].[AspNetRoleClaims]([RoleId] ASC);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment