February 23, 2007

Creating DotNetNuke Host Account Via SQL

Everyone gets locked out of their system at one time or another, especially if you have multiple users working with the same user account. This can be a nightmare to resolve but I hope to make everything a bit easier with this quick tutorial on how to create a new DotNetNuke host account via the database.


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 as these instructions have NOT been tested on any other versions.


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 measures and backup your database before proceeding, I cannot be liable for any data loss that may occur from you performing these options.

Obtain Needed Information

The first step of the process to creating a new host 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.  The remaining items we will need are the "password", "passwordSalt" and "PasswordFormat" values from the aspnet membership table.  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'

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.

DECLARE @ApplicationName nvarchar(256)
SET @ApplicationName 'DotNetNuke'
DECLARE @UserName nvarchar(256
SET @UserName 'NewUserAccount' --The new user
DECLARE @Password nvarchar(128)
SET @Password 'EZxbtno0LG5cdFpiJrENwIKArCC3ZqE+VnxF6TV10LbS+C9bxxBBgw=='
DECLARE @PasswordSalt nvarchar(128)
SET @PasswordSalt 'XEW7SCZvyAx4pr1S/TdoTg=='
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
@IsApproved 1
DECLARE @CurrentTimeUtc datetime 
@CurrentTimeUtc = GETDATE()
DECLARE @CreateDate datetime
@CreateDate @CurrentTimeUtc
DECLARE @UniqueEmail int
@UniqueEmail 0
DECLARE @PasswordFormat int
@PasswordFormat --NOTE: Value from existing user!
Declare @UserId uniqueidentifier

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

--Display the new user id
SELECT 'New User Account Id ', @userId

--Insert the record into the DotNetNuke users table
INSERT INTO users (Username, FirstName, LastName, IsSuperUser, Email,
                    DisplayName, UpdatePassword)
VALUES(@Username, 'New''Account'1, @Email, 'New Account'0)

If everything worked correctly you should see a result set in query analyser (Or SQL Management Studio) that shows New User Id and a GUID value.

Logging In!

You should now be able to navigate to your DNN site and login 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.

tags: DNN, Tutorials, SQL
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.