Migrate Membership to Identity transfer SQL ASPNETDB to Identity
in

One SQL script to copy data from ASPNETDB to existin new Identity databe 

BEGIN TRANSACTION MigrateASPNETDB

/*Set ApplicationId from ASPNETDB*/

DECLARE @ApplicationId uniqueidentifier = '42B0D3A5-2DB7-42E9-B10B-9E2903XXX7FB';

/*Copy Users*/

INSERT INTO [AspNetUsers](

Id,

UserName,

PasswordHash,

SecurityStamp,

EmailConfirmed,

PhoneNumber,

PhoneNumberConfirmed,

TwoFactorEnabled,

LockoutEndDateUtc,

LockoutEnabled,

AccessFailedCount)

SELECT 

aspnet_Users.UserId,

aspnet_Users.UserName,

(aspnet_Membership.Password+'|'+CAST(aspnet_Membership.PasswordFormat as varchar)+'|'+aspnet_Membership.PasswordSalt),

NewID(),

'true',

NULL,

'false',

'true',

aspnet_Membership.LastLockoutDate,

'true',

'0'

FROM [ASPNETDB].dbo.[aspnet_Users]

LEFT OUTER JOIN [ASPNETDB].dbo.[aspnet_Membership] ON aspnet_Membership.ApplicationId = aspnet_Users.ApplicationId 

AND aspnet_Users.UserId = aspnet_Membership.UserId

AND aspnet_Users.ApplicationId = @ApplicationId 

 

 

/* Migrate user question/answer */

  INSERT INTO AspNetUsersExt (UserId, SecurityQuestion, SecurityAnswer, SecurityAnswerSalt)

  SELECT aspnet_Users.UserId, aspnet_Membership.PasswordQuestion, PasswordAnswer, PasswordSalt

  FROM [ASPNETDB].dbo.aspnet_Users

  LEFT OUTER JOIN [ASPNETDB].dbo.aspnet_Membership ON aspnet_Membership.ApplicationId = aspnet_Users.ApplicationId 

  AND aspnet_Users.UserId = aspnet_Membership.UserId

  LEFT OUTER JOIN AspNetUsersExt ON aspnet_Membership.UserId = AspNetUsersExt.UserId

  LEFT OUTER JOIN AspNetUsers ON aspnet_Membership.UserId = AspNetUsers.Id

  WHERE AspNetUsers.Id IS NOT NULL AND AspNetUsersExt.UserId IS NULL

  AND aspnet_Users.ApplicationId = @ApplicationId 

 

  /*Migrate roles*/

  INSERT INTO AspNetRoles ([Id], [Name])

  SELECT [RoleId], [RoleName]

  FROM [ASPNETDB].[dbo].[aspnet_Roles]

  WHERE

/* Skip existing Roles   NOT aspnet_Roles.RoleId in (SELECT Id from  AspNetRoles) AND */

   aspnet_Roles.ApplicationId = @ApplicationId 

   /*Copy Role/User assignments */

   INSERT INTO [dbo].[AspNetUserRoles] (UserId, RoleId)

   SELECT aspnet_UsersInRoles.[UserId],aspnet_UsersInRoles.[RoleId] 

   FROM [ASPNETDB].[dbo].[aspnet_UsersInRoles]

   INNER JOIN [dbo].[AspNetUsers] on AspNetUsers.Id = aspnet_UsersInRoles.[UserId]

   INNER JOIN [dbo].[AspNetRoles] on AspNetRoles.Id = aspnet_UsersInRoles.[RoleId]

 

IF @@ERROR <> 0 

  BEGIN 

    ROLLBACK TRANSACTION MigrateUsers

    RETURN

  END

COMMIT TRANSACTION MigrateASPNETDB

0
Your rating: Нет

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Доступны HTML теги: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <p> <br><b><i> <img>
  • Строки и параграфы переносятся автоматически.

Подробнее о форматировании

КАПЧА
Этот тест необходим защиты от спама
7 + 13 =
Решите эту простую математическую задачу и введите результат. То есть для 1+3, введите 4.
To prevent automated spam submissions leave this field empty.