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.
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 as these
instructions have NOT been tested on any other 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 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 = 'TestingAccount@test.com' --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 @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.
Posted by Mitchel on Friday, February 23, 2007
Name (required)
Email (required)
Website