Is there a way to import a long list of new users to DNN

Question

I have a new DNN site and a long list of users that must be added. How can I create these new users with a script.

I have seen modules for sale but if possible I would like to avoid buying a module.

Answer 1

You can try this out http://blogs.snapsis.com/CommentView,guid,c9d3082b-d7b1-4dfb-8c9c-67c0e864964d.aspx

Answer 2

I'm working with DNN 4.4 and 4.9 and had the same problem. My solution to this was to develop a DNN module for this that could import from CSV or TAB separated text file. My advice would be to either buy or develop a module.

If you are intersted in writting your own I could provide you with code snippets what to do. Just let me know...

Answer 3

Snowcovered has a few built modules out there - (http://www.snowcovered.com/snowcovered2/Default.aspx?tabid=295&search=import+user) -

I have also written scripts to do this from one or anther of spreadsheets or some other database

A sql script to do this really just needs to add the right info

heres most a script i use to import from some access dbs - may not be great or elegant but it works!

SET NOCOUNT ON;
declare @SQLText nvarchar(max)
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

BEGIN TRY
BEGIN TRANSACTION
    -- Insert statements for procedure here
    DECLARE @ApplicationName nvarchar(256)
    SET @ApplicationName = 'DotNetNuke'


    DECLARE @LastName nvarchar(max) 
    DECLARE @FirstName nvarchar(max) 
    DECLARE @EmployeeID nvarchar(max) 
    DECLARE @ContactEmail nvarchar(max)


    DECLARE UserLoop CURSOR Fast_Forward
    FOR SELECT [Employee] as EmployeeID, isnull([LastName],'') as employee_Lastname, isnull([FirstName],'') as emplyee_firstname, '[email protected]' as contactemail
     from openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\_projects\hc.xls;HDR=YES', 'SELECT *  FROM [Sheet1$]')
        --WHERE CustomerID in (SELECT OldID FROM dbo.RCIWebApps_LocationInfo WHERE OwnerID = @NewCustomerNumber)

    Open UserLoop


    FETCH NEXT FROM UserLoop INTO @EmployeeID, @LastName, @FirstName, @ContactEmail

    WHILE @@FETCH_STATUS = 0
        BEGIN
            ----

            ---Make unique usernames - adding one to the list until it doesnt match
            DECLARE @NewUsername nvarchar(max)
            SET @NewUserName = REPLACE(@Firstname + '_' + @LastName, ',','_')
            DECLARE @UserNameTest int
            SET @UsernameTest = 1
            DECLARE @UserNameNumber int
            SET @UserNameNumber = 1
            DECLARE @UserNameTemp nvarchar(max)
            SET @UserNameTemp = @NewUserName
            WHILE @UserNameTest > 0  
                BEGIN
                    SELECT @UsernameTest = count(*) FROM rcidnn..Users WHERE Username = @UserNameTemp  
                    IF @USernametest > 0 
                        BEGIN                   
                            SET @UsernameTemp = @NewUserName + convert(nvarchar(max),@UserNameNumber)
                            SET @UserNameNumber = @UserNameNumber + 1                      
                        END
                END

            SET @NewUserName = @UserNameTemp
            ----
            DECLARE @Password nvarchar(128)
            --From the existing user
            SET @Password = 'iIFBTBSKXithnIDFau1miTih903eL/z5qgbcyMOflIM='

            DECLARE @PasswordSalt nvarchar(128)
            --From the existing user
            SET @PasswordSalt = '4DmxPzt6MYScaGuwNHeUDw=='

            DECLARE @PasswordQuestion nvarchar(256)
            SET @PasswordQuestion = ''

            DECLARE @PasswordAnswer nvarchar(128)
            SET @PasswordAnswer = ''

            DECLARE @IsApproved bit
            SET @IsApproved = 1

            DECLARE @CurrentTimeUtc datetime 
            SET @CurrentTimeUtc = GETDATE()

            DECLARE @CreateDate datetime
            SET @CreateDate = @CurrentTimeUtc

            DECLARE @UniqueEmail int
            SET @UniqueEmail = 0

            DECLARE @PasswordFormat int
            SET @PasswordFormat = 2 --NOTE: Value from existing user!

            DECLARE @PortalId int
            SET @PortalId = 6    --The id of your portal

            Declare @UserId uniqueidentifier
            DECLARE @DNNUserId int

            --Make the stored procedure call
            EXEC rcidnn.dbo.aspnet_Membership_CreateUser @ApplicationName, @NewUsername, @Password,
                            @PasswordSalt, @contactemail, @passwordquestion, @PasswordAnswer, 
                            @IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,
                            @PasswordFormat, @UserId

            --Insert the record into the DotNetNuke users table
print @NewUsername
            INSERT INTO rcidnn..users (Username, FirstName, LastName, IsSuperUser, Email,
                                DisplayName, UpdatePassword)
                VALUES(@NewUsername, @FirstName, @LastName, 0, @ContactEmail, @Firstname + ' ' + @LastName, 0)

            --Get the new userid, from the DNN users table
            SELECT @dnnuserid = userid
            FROM rcidnn..Users
            WHERE username = @NewUsername

            --Now, insert the record into the user portals table
            INSERT INTO rcidnn..UserPortals (userId, PortalId, CreatedDate)
                VALUES(@dnnuserid, @PortalId, GETDATE()) 

            --Now Give the user permissions to the RECISTERED Users group
            INSERT INTO rcidnn..UserRoles (userId, roleId)
            SELECT @dnnuserid,
                    roleId
            FROM rcidnn..Roles
            WHERE (RoleName = 'Registered Users' or RoleName = 'Subscribers' or RoleName = 'G - Location View')
                and portalid = @PortalId 

            --ADD info into profile?? 
            INSERT rcidnn.dbo.UserProfile
            VALUES ( @DNNUserID, 167, @NewCustomerNumber, null, 2, getdate())


            ----
            FETCH NEXT FROM UserLoop INTO @EmployeeID, @LastName, @FirstName, @ContactEmail

        END
    CLOSE Userloop
    DEALLOCATE userloop 

COMMIT
    Print 'Transfered'
END TRY

BEGIN CATCH
    -- Whoops, there was an error
    IF @@TRANCOUNT > 0
     ROLLBACK

    -- Raise an error with the details of the exception
    SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

    RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Answer 4

I have had good luck using this module to import users. DNNVillage Import Export User Pro It imports and exports users using an easily modified XML file. If you use excel to insert your user list into the xml file and then import it you should be good to go. The best part is that the module is free.