Migration from ASP.NET Membership to SimpleMembership

migration.sql

--This script attempts to migrate the users
--from the old ASP.Net Membership tables
--into the new SimpleMemberShip tables
--The SimpleMembsership uses more secure password hashing, as such the old password hashes won't
--work with the SimpleMembership tables.
--As far as I can tell you have two options
--1) Get your users to reset their passwords
--2) Update your web.config to force SimpleMembership to use the old SHA1 format.  See more details 
--here http://stackoverflow.com/questions/12236533/migrating-from-asp-net-membership-to-simplemembership-in-mvc4-rtm


--Use at your own risk
--This has not been tested
--Backup your database before using

--Assumes your using vanilla membership tables
--Adds an additional column to the UserProfile
--table to store the user email address

--Does not migrate roles or profile information

--Please add any comments or enhancements to this gist and I'll update

Delete From webpages_Membership
Delete From UserProfile


ALTER TABLE UserProfile ADD Email NVARCHAR(256) NULL ;
GO


DECLARE @MemberShip TABLE
(UserId int,
 CreateDate datetime,
 ConfirmationToken nvarchar(128),
 IsConfirmed bit,
 LastPasswordFailureDate datetime,
 PasswordFailuresSinceLastSuccess int,
 Password nvarchar(128),
 PasswordChangedDate datetime,
 PasswordSalt nvarchar(128),
 PasswordVerificationToken nvarchar(128),
 PasswordVerificationTokenExpirationDate datetime,
 OldUserID uniqueidentifier,
 Email nvarchar(256),
 UserName nvarchar(256)
)


INSERT INTO @MemberShip
		   ([UserId]
		   ,[CreateDate]
           ,[ConfirmationToken]
           ,[IsConfirmed]
           ,[LastPasswordFailureDate]
           ,[PasswordFailuresSinceLastSuccess]
           ,[Password]
           ,[PasswordChangedDate]
           ,[PasswordSalt]
           ,[PasswordVerificationToken]
           ,[PasswordVerificationTokenExpirationDate]
           ,[OldUserID]
           ,[Email]
           ,[UserName]
           )
Select UserID=(ROW_NUMBER() OVER ( ORDER BY AM.UserId ASC )),CreateDate=CreateDate,ConfirmationToken=null,IsConfirmed=IsApproved,
	   LastPasswordFailureDate= null,PasswordFailuresSinceLastSuccess=0,
	   Password=Password, PasswordChangedDate=LastPasswordChangedDate,
	   PasswordSalt=PasswordSalt,PasswordVerificationToken=Null,
	   PasswordVerificationTokenExpirationDate  = Null,
	   OldUserID = AM.UserId,Email = LoweredEmail,
	   UserName = AU.UserName
FROM   aspnet_Membership AM
JOIN aspnet_Users AU On Am.UserId = AU.UserId



INSERT INTO dbo.webpages_Membership
		   ([UserId]
		   ,[CreateDate]
           ,[ConfirmationToken]
           ,[IsConfirmed]
           ,[LastPasswordFailureDate]
           ,[PasswordFailuresSinceLastSuccess]
           ,[Password]
           ,[PasswordChangedDate]
           ,[PasswordSalt]
           ,[PasswordVerificationToken]
           ,[PasswordVerificationTokenExpirationDate]
           )
Select UserID,CreateDate,ConfirmationToken,IsConfirmed,
	   LastPasswordFailureDate,PasswordFailuresSinceLastSuccess,
	   Password, PasswordChangedDate,
	   PasswordSalt,PasswordVerificationToken,
	   PasswordVerificationTokenExpirationDate
FROM @MemberShip  

SET IDENTITY_INSERT UserProfile ON

INSERT INTO [UserProfile]
		   ([UserId]
		   ,[UserName]
		   ,[Email])
		   Select UserID,UserName,Email  
		   FROM @MemberShip  

SET IDENTITY_INSERT UserProfile OFF