Creating a Standard DotNetNuke User via sql

Due to popular demand from my "Creating a Host Account Via SQL" article, this article is an extension of that article to show you how to create a regular portal user based on the password for an existing user account. The first portions of this process are very similar to that of the host account creation, however, there are a few additional steps.

Requirements

For these instructions to work properly you MUST know the password of at least one user account in your portal. This can be any user account as long as it is for the same application! Also, you should be running DotNetNuke 4.4.1 or later as these instructions have NOT been tested on any previous versions.

Disclaimer

I am providing this example as exactly that an example. Anytime you directly modify the database you are introducing a level of risk. Please take the appropriate safety precautions and back up your database before proceeding, I can not be liable for any data loss that may occur from you performing these steps.

Obtain Needed Information

The first step of the process to creating a new user account via the database is to obtain some needed information. We need the "Application Name" for your DotNetNuke installation, typically this is simply "dotnetnuke", it is set in your web config under the membership element. We also need the "password", "passwordSalt" and "PasswordFormat" values from the aspnet membership table for a working user account with a known password. Below is a query to obtain this information. NOTE: replace the text TestUser with the username of your known working user account.

SELECT m.password, m.passwordsalt, m.passwordformat
FROM aspnet_users u
    INNER JOIN aspnet_membership m
        ON (u.userid = m.userid)
WHERE u.UserName = 'TestUser'

The final piece of information that we need is the id of your portal, this can be obtained from the "Portals" table, below is a query that will give you the portal name and portalId of each portal in your DNN installation.

SELECT PortalId,
     PortalName
FROM Portals

Execute Scripts to Create Account

Now we simply combine our desired new information and the existing information that we obtained to create the user account. Below is a listing of the SQL Code that I used to create the account in my portal. You will need to substitute your own values for the various properties. Also, I am using the long format to accomplish this goal by declaring each of the variables prior to calling the stored procedure, this is simply done to make the code easier to read. You will also want to change the hardcoded values for FirstName, LastName, and DisplayName. I did not declare variables for those to reduce the length of the sample code.

DECLARE @ApplicationName nvarchar(256)
SET @ApplicationName = 'DotNetNuke'
DECLARE @UserName nvarchar(256) 
SET @UserName = 'NewUser' --The new user
DECLARE @Password nvarchar(128)
--From the existing user
SET @Password = 'LLSXX8xW6+0EbrV4JBzL/YenA1D6BBfRnkYY7FtQvNGmmPOhVdPiAA=='
DECLARE @PasswordSalt nvarchar(128)
--From the existing user
SET @PasswordSalt = 'P40ky5tExsx37nUIFnCWZQ=='
DECLARE @Email nvarchar(256)
SET @Email = '[email protected]' --You can set this to whatever you want
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 = 0    --The id of your portal
Declare @UserId uniqueidentifier
DECLARE @DNNUserId int

--Make the stored procedure call
EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @Username, @Password,
                @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(@Username, 'My', 'NewAccount', 0, @Email, 'New Account', 0)

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

--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 RECISTERED Users group
INSERT INTO UserRoles (userId, roleId)
SELECT @dnnuserid,
        roleId
FROM Roles
WHERE RoleName = 'Registered Users'

The script should execute successfully with simple messages regarding record counts, no error messages should be displayed. To explain a bit more what the above script does. First the "Declare" and "Set" statements simply build parameters for use later. We then create the ASP.NET membership records for our new user. We then create our DotNetNuke user record to ensure that DNN is aware of the user. Then we obtain the id of the added user and then create a UserPortals record to grant the user access to the specified portal.

Logging In!

You should now be able to navigate to your DNN site and log in with the new user account and the password from the account you copied. Upon login, you should have full host permissions!

If you have any questions or comments regarding this please let me know! This is considered a last resort, however, it is something that can be used in times of need.