DNN Forums

Ask questions about your website to get help learning DNN and help resolve issues.

Forums
Search Forum
Advanced SearchTopicsPosts

Adding User Via SQL | Updating User ID

You are not authorized to post a reply.

Forums > SQL and SQL Server

Sort:

Author

Messages

JordanM

New Around Here

Posts:16

6/27/2021 9:48 PM

Hi all,
All users from my websites had been permanently deleted, these users had a re-occuring payment via paypal. Each Payment would send a notification to the website, extending a expiry date for the subscription role on that user. The Unique identifier is the "UserID".

Once a user is deleted the UserID is gone. I can create a user via SQL scipt below - I then update DBO.profile, DBO.userprofile, DBO.Userportal,DBO.Userroles. - which shows the profile in DNN. But when I try to login to a user - i'm having password Issues. Any help is much appricated!!!!

SET IDENTITY_INSERT dbo.Users ON
INSERT INTO dbo.Users (UserID, Username, FirstName, LastName, IsSuperUser, AffiliateId, Email, DisplayName, UpdatePassword, LastIPAddress, IsDeleted, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate, PasswordResetToken, PasswordResetExpiration)
VALUES ( 01, '[email protected]', 'name', 'name', 0, NULL, '[email protected]', 'Name', 0, NULL, 0, 1, NULL, 1, NULL, NULL, NULL);Go

SET IDENTITY_INSERT dbo.Users OFF
GO

How do I set a password or send a password reset link for a user created via SQL? Is there another table i am missing?

Michael Tobisch

Veteran Member

Posts:878

6/28/2021 5:00 AM

Hi,
this is not an easy task, anyway, it should be possible.
A while ago I created an SQL script that generates 999 users for testing purposes (GitHub - MaiklT/GenerateDNNTestUsers: An SQL script to create 999 Test Users for DNN), have a look at it, it should give you the idea.

To create a password (I used the same for all users), you have to create a user the "normal" way and use the password hash and salt in the aspnet_membership table.

Don't forget to restart the application pool when you're done - and I would strongly recommend to try this in a testing environment (copy of your web site) before you do it in production. And always create a backup before you do it, in the testing environment as well as in the production environment.

Happy DNNing!
Michael

Michael Tobisch
DNN★MVP

Sebastian Leupold

Veteran Member

Posts:529

6/28/2021 5:50 PM

please be aware, creating user records is complex, besides DNN tables, you need to populate records in the aspnet membership and users table as well. Therefore you should stay away from creating user records manually in the database. you might use DNN Prompt to create users manually in DNN (see https://dnndocs.com/content/features/tools-utilities/prompt/index.html)

drcz

New Around Here

Posts:18

12/25/2021 1:20 PM

Hi Michael,
I did followed your script for the 999 users and my question is how to create the password hash and salt in the aspnet_membership table out of a normal password? I want to create a custom registration form instead of the default DNN.

Michael Tobisch

Veteran Member

Posts:878

12/27/2021 2:11 AM

The script is only meant for creating users in a testing environment, it is not an example of how to create a user using SQL (even if is doing exactly this). In a production environment you should use the API.

To create a custom registration form you can either follow the steps described here: Replacing Registration page with custom and blocking the default Register Page (dnnsoftware.com) or use a third-party module, eg Action Form - Custom DNN Forms | DNN Sharp.

Happy DNNing!
Michael

Michael Tobisch
DNN★MVP

drcz

New Around Here

Posts:18

12/27/2021 2:31 AM

Thank you Michael,
the issue for me is not to create a custom registration page or to use third-party (I do have one) but to stop the verification email that DNN trigger when I use the DNN registration of the third-party module. I need to get arround the DNN default to do my own registration in the SQL tables or find a way to stop the DNN notification for verified users. Any idea with that?

Michael Tobisch

Veteran Member

Posts:878

12/27/2021 2:58 AM

In this case you should ask the module's vendor if it's possible to deactivate this behaviour from within the module - and if not to do a "feature request".

Happy DNNing!
Michael

Michael Tobisch
DNN★MVP

You are not authorized to post a reply.

These Forums are dedicated to discussion of DNN Platform.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

If you have (suspected) security issues, please do not post them in the forums but send an email to [email protected]
No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
No Flaming or Trolling.
No Profanity, Racism, or Prejudice.
Site Moderators have the final word on approving / removing a thread or post or comment.
English language posting only, please.

Would you like to help us?

Awesome! Simply post in the forums using the link below and we'll get you started.

Get Involved