I've been on a 2 month hiatus, but I've come back with some new skills. I've been working on a DotNetNuke portal to replace our existing MojoPortal. I really wanted to like MojoPortal because of it's simplicity and ease of setup, but in the end MojoPortal Core development pretty much came to a halt. There are just too many standard features that are missing, plus DNN has so much out of the box, I couldn't resist. Plus, the community behind DNN is huge.
While browsing Mitchel Sellers' blog, I found some SQL that allows me to migrate the 700+ user accounts in our MojoPortal to DotNetNuke. I've put it in a script and parametized it.
After I created this, I created a table from my old MojoPortal, which contains: UserName, FirstName, LastName, DisplayName, Email, and RoleName (Primary Role, which must exist in DNN before running the script). Then, I ran it through a cursor and added all the users.
** ADDITION NOTE: If you are using {owner}{prefix} in DNN, you'll need to modify the script.
-- =======================================================================================================
-- Author: Larry Eisenstein
-- Create date: 2/15/2011
-- Description: Creates a DNN User by copying an existing user.
-- This can be used to script a single account creation or migrating users from another system.
-- This works by creating the NewUser from an existing user.
-- The script was pulled from Mitchel Sellers website. He has a great blog, so you should visit it.
-- http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/84/creating-a-standard-dotnetnuke-user-via-sql.aspx
--
--
-- Req:
-- 1. Know the Username/Password of an existing user. The password for the user you create will be the password
-- of this user
-- 2. If you are assigning Roles, the RoleName MUST existing in the DotNetNuke site
--
-- Defaults: I set up some defaults, but these can be changed via params or just change the defaults in the script.
--
-- Notes:
-- Stored Procs used: aspnet_Membership_CreateUser
-- Tables Updated: users, Roles, UserPortals
-- The password works by copying the encrypted password, passwordsalt of the ExistingUser to your new user. Then, you can
-- just login with that user's password.
-- ==========================================================================================================
CREATE PROCEDURE [dbo].[AAA_MigrateUser_sp]
@ApplicationName varchar(255) = 'DotNetNuke', -- Search for applicationName in your web.config
@ExistingUserName varchar(255) = 'TestUser', -- This must be an existing DNN user
@FirstName varchar(255) = 'Migrated',
@LastName varchar(255) = 'UserAccount',
@DisplayName varchar(255) = 'Migrated UserAccount',
@NewUserName varchar(255),
@RoleName varchar(255) = 'Registered Users',
@PortalId int = 0,
@Email nvarchar(256) = 'TestUser@email.org'
AS
BEGIN
DECLARE @PasswordQuestion varchar(256),
@PasswordAnswer varchar(256),
@Pw varchar(255),
@PasswordSalt varchar(255),
@PasswordFormat int,
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime,
@UniqueEmail int,
@UserId uniqueidentifier,
@DNNUserId int,
@NumUsers int
SELECT @PasswordQuestion = '',
@PasswordAnswer = '',
@IsApproved = 1,
@CurrentTimeUtc = GETDATE(),
@CreateDate = @CurrentTimeUtc,
@UniqueEmail = 0
SELECT @NumUsers = COUNT(*)
FROM aspnet_Users
WHERE UserName = @NewUserName
IF(@NumUsers != 0)
return -1
SELECT @Pw = m.password,
@PasswordSalt = m.passwordsalt,
@PasswordFormat = m.passwordformat
FROM aspnet_users u
INNER JOIN aspnet_membership m ON (u.userid = m.userid)
WHERE u.UserName = @ExistingUserName
-- Make the stored procedure call
EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @NewUserName, @Pw,
@PasswordSalt, @email, @passwordquestion, @PasswordAnswer,
@IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,
@PasswordFormat, @UserId
-- Insert the record into the DotNetNuke users table
INSERT INTO users (Username, FirstName, LastName, IsSuperUser, Email,
DisplayName, UpdatePassword)
VALUES(@NewUserName, @FirstName, @LastName, 0, @Email, @DisplayName, 0)
-- Get the new userid, from the DNN users table
SELECT @dnnuserid = userid
FROM Users
WHERE username = @NewUserName
-- Now, insert the record into the user portals table
INSERT INTO UserPortals (userId, PortalId, CreatedDate)
VALUES(@dnnuserid, @PortalId, GETDATE())
-- Now Give the user permissions to the User Group you specified
IF(@RoleName != 'Registered Users' and @RoleName IS NOT NULL)
BEGIN
INSERT INTO UserRoles (userId, roleId)
SELECT @dnnuserid,
roleId
FROM Roles
WHERE RoleName = @RoleName
END
-- Now Give the user permissions to the REGISTERED Users group
INSERT INTO UserRoles (userId, roleId)
SELECT @dnnuserid,
roleId
FROM Roles
WHERE RoleName = 'Registered Users'
END
