Migrating database from ASP.NET Identity to ASP.NET Core Identity
-- STEP 1 : Change name of existing tables | |
EXEC sp_rename 'AspNetRoles', 'AspNetRoles_old'; | |
EXEC sp_rename 'AspNetUserClaims', 'AspNetUserClaims_old'; | |
EXEC sp_rename 'AspNetUserLogins', 'AspNetUserLogins_old'; | |
EXEC sp_rename 'AspNetUserRoles', 'AspNetUserRoles_old'; | |
EXEC sp_rename 'AspNetUsers', 'AspNetUsers_old'; | |
-- STEP 2 : Create ASP.NET Core Identity tables | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[AspNetRoleClaims]( | |
[Id] [int] IDENTITY(1,1) NOT NULL, | |
[RoleId] [nvarchar](450) NOT NULL, | |
[ClaimType] [nvarchar](max) NULL, | |
[ClaimValue] [nvarchar](max) NULL, | |
CONSTRAINT [PK_AspNetRoleClaims] 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 | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[AspNetRoles]( | |
[Id] [nvarchar](450) NOT NULL, | |
[Name] [nvarchar](256) NULL, | |
[NormalizedName] [nvarchar](256) NULL, | |
[ConcurrencyStamp] [nvarchar](max) NULL, | |
CONSTRAINT [PK_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] TEXTIMAGE_ON [PRIMARY] | |
GO | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[AspNetUserClaims]( | |
[Id] [int] IDENTITY(1,1) NOT NULL, | |
[UserId] [nvarchar](450) NOT NULL, | |
[ClaimType] [nvarchar](max) NULL, | |
[ClaimValue] [nvarchar](max) NULL, | |
CONSTRAINT [PK_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 | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[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 CLUSTERED | |
( | |
[LoginProvider] ASC, | |
[ProviderKey] 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 | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
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 | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[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](7) NULL, | |
[LockoutEnabled] [bit] NOT NULL, | |
[AccessFailedCount] [int] NOT NULL, | |
CONSTRAINT [PK_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] | |
GO | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[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 CLUSTERED | |
( | |
[UserId] ASC, | |
[LoginProvider] ASC, | |
[Name] 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 | |
SET ANSI_PADDING ON | |
GO | |
CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId] ON [dbo].[AspNetRoleClaims] | |
( | |
[RoleId] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex] ON [dbo].[AspNetRoles] | |
( | |
[NormalizedName] ASC | |
) | |
WHERE ([NormalizedName] IS NOT NULL) | |
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE NONCLUSTERED INDEX [IX_AspNetUserClaims_UserId] ON [dbo].[AspNetUserClaims] | |
( | |
[UserId] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE NONCLUSTERED INDEX [IX_AspNetUserLogins_UserId] ON [dbo].[AspNetUserLogins] | |
( | |
[UserId] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_RoleId] ON [dbo].[AspNetUserRoles] | |
( | |
[RoleId] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE NONCLUSTERED INDEX [EmailIndex] ON [dbo].[AspNetUsers] | |
( | |
[NormalizedEmail] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
GO | |
SET ANSI_PADDING ON | |
GO | |
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex] ON [dbo].[AspNetUsers] | |
( | |
[NormalizedUserName] ASC | |
) | |
WHERE ([NormalizedUserName] IS NOT NULL) | |
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
GO | |
ALTER TABLE [dbo].[AspNetRoleClaims] WITH CHECK ADD CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY([RoleId]) | |
REFERENCES [dbo].[AspNetRoles] ([Id]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[AspNetRoleClaims] CHECK CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] | |
GO | |
ALTER TABLE [dbo].[AspNetUserClaims] WITH CHECK ADD CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY([UserId]) | |
REFERENCES [dbo].[AspNetUsers] ([Id]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] | |
GO | |
ALTER TABLE [dbo].[AspNetUserLogins] WITH CHECK ADD CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY([UserId]) | |
REFERENCES [dbo].[AspNetUsers] ([Id]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] | |
GO | |
ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY([RoleId]) | |
REFERENCES [dbo].[AspNetRoles] ([Id]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] | |
GO | |
ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY([UserId]) | |
REFERENCES [dbo].[AspNetUsers] ([Id]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] | |
GO | |
ALTER TABLE [dbo].[AspNetUserTokens] WITH CHECK ADD CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY([UserId]) | |
REFERENCES [dbo].[AspNetUsers] ([Id]) | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[AspNetUserTokens] CHECK CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] | |
GO | |
-- STEP 3 : Migrate data from old tables (ASP.NET Identity) to new tables (ASP.NET Core Identity) | |
INSERT INTO AspNetRoles ([Id], [Name], [NormalizedName], [ConcurrencyStamp]) | |
SELECT [Id], [Name], UPPER([Name]), LOWER(NEWID()) FROM AspNetRoles_old; | |
INSERT INTO AspNetUsers ([Id], [UserName], [NormalizedUserName], [Email], [NormalizedEmail], [EmailConfirmed], [PasswordHash], [SecurityStamp], [ConcurrencyStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEnd], [LockoutEnabled], [AccessFailedCount]) | |
SELECT [Id], [UserName], UPPER([UserName]), [Email], UPPER([Email]), [EmailConfirmed], [PasswordHash], [SecurityStamp], LOWER(NEWID()), [PhoneNumber], [PhoneNumberConfirmed], 0, null, 1, 0 FROM AspNetUsers_old; | |
INSERT INTO AspNetUserRoles ([UserId], [RoleId]) | |
SELECT [UserId], [RoleId] FROM AspNetUserRoles_old; |
This comment has been minimized.
This comment has been minimized.
Thanks so much for this, I spent ages looking for ways to migrate to ASP.NET Core. Turns out this script was all I needed! |
This comment has been minimized.
This comment has been minimized.
Hi,This is great man, thanks for using it.On Jul 30, 2020 9:13 PM, Mark Eastwood <notifications@github.com> wrote:@MarkioE commented on this gist.
Thanks so much for this, I spent ages looking for ways to migrate to ASP.NET Core. Turns out this script was all I needed!
—You are receiving this because you authored the thread.Reply to this email directly, view it on GitHub, or unsubscribe.
|
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
You should not miss my article on this https://www.itorian.com/2019/10/Migrating-database-from-ASP-NET-Identity-to-ASP-NET-Core-Identity.html