Migrate Sitecore users from One instance to another

I created a SQL script which helps in migrating users from one Sitecore instance to another.

If you want to use this to change the source and destination core database names. Make sure you take backup of your databases before running the script so that if anything goes wrong you can restore it easily.

I have not tested this for all version/scenarios so make your cautious decision to use this and test it locally before running it on production/live environment.

Here is the script that I have used.

/*********************************************

INSERT INTO [Destination_Core]. dbo.aspnet_Users
SELECT * FROM [Source_Core] .dbo. aspnet_Users
WHERE NOT EXISTS
(SELECT *
FROM [Destination_Core]. dbo.aspnet_Users
WHERE [Destination_Core]. dbo.aspnet_Users.UserName COLLATE DATABASE_DEFAULT
= [Source_Core]. dbo.aspnet_Users.UserName COLLATE DATABASE_DEFAULT)

INSERT INTO [Destination_Core]. dbo.aspnet_Profile
SELECT * FROM [Source_Core] .dbo. aspnet_Profile
WHERE NOT EXISTS
(SELECT *
FROM [Destination_Core]. dbo.aspnet_Profile
WHERE [Destination_Core]. dbo.aspnet_Profile .UserId
= [Source_Core]. dbo.aspnet_Profile .UserId)

DECLARE @ApplicationID uniqueidentifier
SET @ApplicationID = (select ApplicationId from [Destination_Core]. dbo.aspnet_Applications)

INSERT INTO [Destination_Core]. dbo.aspnet_Membership (ApplicationID,UserID,[Password],PasswordFormat,PasswordSalt,Email,LoweredEmail,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment )
SELECT @ApplicationID as ApplicationID,UserID,[Password],PasswordFormat,PasswordSalt,Email,LoweredEmail,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
FROM [Source_Core] .dbo.aspnet_Membership
WHERE NOT EXISTS
(SELECT *
FROM [Destination_Core]. dbo.aspnet_Membership
WHERE [Destination_Core]. dbo.aspnet_Membership .UserId
= [Source_Core]. dbo.aspnet_Membership .UserId)

DECLARE @RoleID uniqueidentifier
SET @RoleID = (select RoleID from [Destination_Core]. dbo.aspnet_Roles where RoleName=’User Role’ )

INSERT INTO [Destination_Core]. dbo.aspnet_UsersInRoles (UserID,RoleID )
SELECT DISTINCT UserID, @RoleID as RoleID
FROM [Source_Core] .dbo. aspnet_UsersInRoles
WHERE NOT EXISTS
(SELECT UserID
FROM [Destination_Core]. dbo.aspnet_UsersInRoles
WHERE [Destination_Core]. dbo.aspnet_UsersInRoles .UserId
= [Source_Core]. dbo.aspnet_UsersInRoles .UserId)

************************************************************/