Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Migrating DNN users via SQL from old to new DBMigrating DNN users via SQL from old to new DB
Previous
 
Next
New Post
9/24/2013 8:26 PM
 

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


Entrepreneur

PokerDIY Tournament Manager - PokerDIY Tournament Manager<
PokerDIY Game Finder - Mobile Apps powered by DNN
PokerDIY - Connecting Poker Players

 
New Post
9/25/2013 3:07 AM
 
nice job!

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Migrating DNN users via SQL from old to new DBMigrating DNN users via SQL from old to new DB


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out