As part of my migration from an old DNN site to a brand new DNN 7 site, I had to port all users across. Here is the script that I made for this - it requires understanding of how it works and you should read through it line by line. Of course all users will have a new UserID in the new DB (so all associated data has to be done too). This script may help someone - let me know if you have questions...
DO NOT RUN THIS WITHOUT UNDERSTANDING IT - I take no responsibility ;)
DECLARE UsersToImport Cursor FOR
(
SELECT U.UserID, U.Email, U.UserName
FROM DB1.dbo.Users U
INNER JOIN DB1.dbo.UserPortals UP ON UP.UserID = U.USerID
INNER JOIN DB1.dbo.aspnet_users AU ON AU.UserName = U.UserName
INNER JOIN DB1.dbo.aspnet_Membership AM ON AM.USerID = AU.UserID
WHERE PortalID = 6 AND U.IsDeleted = 0
AND AU.ApplicationID = 'XYZ'
)
OPEN UsersToImport
DECLARE @OldApplicationId nvarchar(256)
DECLARE @NewApplicationId nvarchar(256)
SET @OldApplicationId = 'XYZ'
SET @NewApplicationId = 'ABC'
DECLARE @OldUserId int
DECLARE @NewUserId int
DECLARE @Email varchar(50)
DECLARE @UserName varchar(100)
DECLARE @NewCreatedByUserID int
SET @NewCreatedByUserID = 1
DECLARE @OldRegisteredRole int
SET @OldRegisteredRole = 183
DECLARE @NewRegisteredRole int
SET @NewRegisteredRole = 1
DECLARE @OldAdminRole int
SET @OldAdminRole = 182
DECLARE @NewAdminRole int
SET @NewAdminRole = 0
DECLARE @NewPortalID int
SET @NewPortalID = 0
DECLARE @OldPortalID int
SET @OldPortalID = 6
DECLARE @knownPassword NVARCHAR(128)
--I got this by creating a user with a password and then grabing their record (all users will have the same password - no way around that)
SET @knownPassword = 'GETHISFROMAKNOWNUSER'
DECLARE @knownPasswordSalt NVARCHAR(128)
SET @knownPasswordSalt = 'GETHISFROMAKNOWNUSER'
FETCH NEXT FROM UsersToImport INTO @OldUserId, @Email, @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
--check that this email does not already exist first
IF NOT EXISTS(SELECT UserId FROM Users U1 WHERE U1.Email = @Email)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--use email for username
INSERT INTO [Users] ([Username] ,[FirstName] ,[LastName] ,[IsSuperUser] ,[AffiliateId] ,[Email] ,[DisplayName] ,[UpdatePassword] ,[LastIPAddress] ,[IsDeleted] ,[CreatedByUserID] ,[CreatedOnDate] ,[LastModifiedByUserID] ,[LastModifiedOnDate], OldUserId)
SELECT @Email ,[FirstName] ,[LastName] ,[IsSuperUser] ,[AffiliateId] ,[Email] ,[DisplayName] ,[UpdatePassword] ,[LastIPAddress] ,[IsDeleted] ,[CreatedByUserID] ,[CreatedOnDate] ,[LastModifiedByUserID] ,[LastModifiedOnDate], @OldUserId
FROM DB1.dbo.Users
WHERE UserId = @OldUserId
SELECT @NewUserId = @@IDENTITY FROM [Users]
INSERT INTO [UserPortals] ([UserId] ,[PortalId] ,[CreatedDate] ,[Authorised] ,[IsDeleted] ,[RefreshRoles])
SELECT @NewUserId ,@NewPortalID ,[CreatedDate] ,[Authorised] ,[IsDeleted] ,[RefreshRoles]
FROM DB1.dbo.UserPortals
WHERE UserId = @OldUserId AND PortalID = @OldPortalID
INSERT INTO [aspnet_Users] ([ApplicationId] ,[UserId] ,[UserName] ,[LoweredUserName] ,[MobileAlias] ,[IsAnonymous] ,[LastActivityDate])
SELECT TOP 1 @NewApplicationId ,[UserId] ,@Email ,LOWER(@Email) ,[MobileAlias] ,[IsAnonymous] ,[LastActivityDate]
FROM DB1.dbo.aspnet_Users AU
WHERE UserName = @UserName AND ApplicationID = @OldApplicationId
AND NOT EXISTS (SELECT USerID FROM aspnet_Users UA1 WHERE UA1.UserID = AU.[UserId])
INSERT INTO [aspnet_Membership] ([ApplicationId] ,[UserId] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePIN] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockoutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment])
SELECT @NewApplicationId ,AM.[UserId] ,
--[Password] ,
@knownPassword,
--encrypted
2 ,
--working on this
@knownPasswordSalt ,
--[PasswordSalt],
[MobilePIN] ,AM.[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockoutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment]
FROM DB1.dbo.aspnet_Membership AM
INNER JOIN DB1.dbo.aspnet_users AU ON AU.UserID = AM.UserID
INNER JOIN DB1.dbo.Users U ON AU.UserName = U.UserName
INNER JOIN DB1.dbo.UserPortals UP ON UP.UserID = U.USerID
WHERE AM.Email = @Email AND AM.ApplicationID = @OldApplicationId
AND U.UserID = @OldUserId AND UP.PortalID = @OldPortalID
INSERT INTO [UserAuthentication] ([UserID] ,[AuthenticationType] ,[AuthenticationToken] ,[CreatedByUserID] ,[CreatedOnDate] ,[LastModifiedByUserID] ,[LastModifiedOnDate])
SELECT @NewUserId ,[AuthenticationType] ,[AuthenticationToken] ,[CreatedByUserID] ,[CreatedOnDate] ,[LastModifiedByUserID] ,[LastModifiedOnDate]
FROM DB1.dbo.UserAuthentication UA
WHERE UserId = @OldUserId
AND NOT EXISTS (SELECT * FROM UserAuthentication UA1 WHERE UA1.UserID = @NewUserId AND UA1.[AuthenticationType] = UA.[AuthenticationType])
INSERT INTO [UserRoles] ([UserID] ,[RoleID] ,[ExpiryDate] ,[IsTrialUsed] ,[EffectiveDate] ,[CreatedByUserID] ,[CreatedOnDate] ,[LastModifiedByUserID] ,[LastModifiedOnDate] ,[Status] ,[IsOwner])
SELECT @NewUserId,
ISNULL((SELECT RoleID FROM Roles R1 WHERE R1.RoleName = R.RoleName ), 101) --Catch NULL Role- erase after
,[ExpiryDate] ,[IsTrialUsed] ,[EffectiveDate] ,UR.[CreatedByUserID] ,UR.[CreatedOnDate] ,UR.[LastModifiedByUserID] ,UR.[LastModifiedOnDate] ,UR.[Status] ,[IsOwner]
FROM DB1.dbo.[UserRoles] UR
INNER JOIN DB1.dbo.Roles R ON R.RoleID = UR.RoleID
WHERE [UserID] = @OldUserId
--not using NEwsletter Role anymore or Monthly Updates (NEw USers only)
AND R.RoleID NOT IN (185, 293)
INSERT INTO [UserRoles] ([UserID] ,[RoleID] ,[ExpiryDate] ,[IsTrialUsed] ,[EffectiveDate] ,[CreatedByUserID] ,[CreatedOnDate] ,[LastModifiedByUserID] ,[LastModifiedOnDate] ,[Status] ,[IsOwner])
SELECT @NewUserId, 102, NULL, NULL, GetUTCDate(), 1 ,GetUTCDate() ,1 ,GetUTCDate() ,1 ,0
print 'User Created: ' + @Email
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error with User: ' + @Email
SELECT
@Email AS Email,
@OldUserId AS OldUSerID,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
IF @@Trancount > 0 ROLLBACK TRANSACTION
END CATCH
END
ELSE
BEGIN
PRINT 'Email already exists: ' + @Email
END
FETCH NEXT FROM UsersToImport INTO @OldUserId, @Email, @UserName
END
CLOSE UsersToImport
DEALLOCATE UsersToImport