Creating a Standard DotNetNuke User Via SQL > Mitchel's Personal Web Portal

Current Articles | Categories | Search | Syndication

Creating a Standard DotNetNuke User Via SQL
By Mitchel Sellers on Monday, May 14, 2007 @ 6:47 AM

2791 Views :: 11 Comments ::

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 backup 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 hard coded 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 = '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 @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 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.


	Share/Save This Article
	Use the below controls to save this article to one of many popular social bookmarking sites!


	Article Rating
	12345

Article Comments

By erik @ Tuesday, May 15, 2007 9:33 AM

Does this enter them in the UserRoles table as a "Registered User"

By Mitchel Sellers @ Tuesday, May 15, 2007 11:42 AM

Erik,

I greatly apologize the origional posting did not insert that record. I have updated the script and it now does create the user as a "Registered" user.

By Joe Davies @ Tuesday, May 29, 2007 2:33 AM

Hi there,

I have been looking for this sort of script so I can setup DNN users from a windows application using ADO.

It sort of works and I don't get any errors when running the script. The user information is sitting in all the neccessary tables, but when I attempt to login as the newly created user, I get the following message....
A critical error has occurred.
Object reference not set to an instance of an object.

I also notice that the created date next to the listed users that have been created in this way is blank, though this created date is showing in the membership table.

Are you able to give me any pointers.

Thanks for making this script available to the dotnetnuke community.

Regards

Joe Davies

By Mitchel Sellers @ Tuesday, May 29, 2007 4:17 AM

Joe,

Which data table shows a blank value for the created date?

By Joe Davies @ Tuesday, May 29, 2007 1:27 PM

Hi Mitchell,

I think the created date comes from the aspnet_membership table. This is the table that has the password and passwordSalt. The date is actually set correctly in this table but it isn't displaying in the user list.

I've carefully looked at all the tables that contain a userID field to see if there is anything obviously missing but it all appears fine. I'm still unable to login with the new users credentials. I've created different users but they are all inaccessible.

Mmmh strange one!

Joe

By Mitchel Sellers @ Tuesday, May 29, 2007 5:28 PM

That is a strange one.

There isn't by chance an error message in the event log is there?

Also, you do have the userportals entry that ws added after my update to this article correct?

By Joe Davies @ Wednesday, May 30, 2007 7:14 AM

I've sorted it, I had changed the application name from 'dotnetnuke' to what I thought should have been my database name. I put it back as it was and it works like a dream now.

I have now created a simple function in Microsoft Access which reads in your script from a lookup table. I then use replace tokens in the script as simple parameters. A passthrough query is then created on the fly and bingo a new user appears in the portal. I can also force the resetting of a password by using 1 instead of 0 in the bolSetPassword argument.

This script of yours has been a godsend.

Thanks again!

Joe

For those interested see function below
'===========================
Function CreateUser(strUserID As String, strUserEmail As String, intPortalID As Integer, strFirstName As String, _
strLastName As String, strDisplayName As String, bolSetPassword As Integer)

Dim MyDb As Database, MyQ As QueryDef, strSQL As String
Set MyDb = CurrentDb()

' Create a temporary QueryDef object that is not saved.
Set MyQ = MyDb.CreateQueryDef("")

' A system DSN has to be setup for pass through queries.
MyQ.Connect = "ODBC;DSN=dsn;UID=userid;PWD=password;DATABASE=databasename"

' Set ReturnsRecords to false in order to use the Execute method.
MyQ.ReturnsRecords = False

' look up the script and replace the tokens with function arguments
strSQL = DLookup("[CreateNewUser]", "tblSettings")

strSQL = Replace(strSQL, "<UserID>", strUserID)
strSQL = Replace(strSQL, "<UserEmail>", strUserEmail)
strSQL = Replace(strSQL, "<PortalID>", intPortalID)
strSQL = Replace(strSQL, "<FirstName>", strFirstName)
strSQL = Replace(strSQL, "<LastName>", strLastName)
strSQL = Replace(strSQL, "<DisplayName>", strDisplayName)
strSQL = Replace(strSQL, "<SetPassword>", bolSetPassword)

MyQ.SQL = strSQL
'Debug.Print strSQL
MyQ.Execute
MyQ.Close
MyDb.Close

End Function

By Scott @ Tuesday, September 25, 2007 10:53 AM

I was wondering, would the process for updating passwords for dnn user account be achieved similarly?

By Mitchel Sellers @ Tuesday, September 25, 2007 12:25 PM

Scott,

If you wanted to "copy" a password from an existing account, then yes. If you want to pick a password that another account doesn't have then no.

By Ali @ Thursday, September 27, 2007 9:36 AM

Thanks Mitchel just the thing I was looking for.

Three questions:
1. What is passwordSalt
2. I want to add a number of users and would like to give them unique passwords. is that possible.
3. Is the UserName unique?

By Mitchel Sellers @ Thursday, September 27, 2007 1:38 PM

Ali,

1.) It is used to futher secure the actual password that is stored, I am not 100% sure how the salt is used in the microsoft implementation but it is a standard security practice.

2.) If you want to specifically set a password you will need to go another way, possibly look for a batch import module. I will be working on one to release within the next 2-3 weeks., otherwise John Mitchell over at Snapsis has an .aspx page that you can use to import as well.

3.) Yes, the username must be unique!

Click here to post a comment