Products

Solutions

Learn More

Partners

Community

Blog

About

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Fastest / best way to remove large number of users from DNN website?Fastest / best way to remove large number of users from DNN website?
Previous
 
Next
New Post
8/15/2018 10:25 PM
 

What is the best way to remove a large number of users from a DNN website?

 

Thanks

 

Tom

 
New Post
8/15/2018 11:15 PM
 
Sorry for the multiple threads. Web site has been acting weird, (and not working in some browsers) for a couple of days. I wuld delete the extra threads if I could

Tom
 
New Post
8/16/2018 6:49 AM
 
Do you have any criteria to select those users? Something like UserId between x and y, or last activity date before a specific date?

Happy DNNing!
Michael

Michael Tobisch
DNN★MVP
dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
8/16/2018 5:14 PM
 

Thanks for your reply!

In this case I just want to clear all the users - This is a test server site which was copied from a live site, I'm testing a AD authentication and want to start over with no users. Will probably need to do the same on the live site later as well.

Tom

 
New Post
8/17/2018 7:50 AM
 

Tom,

after making a backup of your database you could do the following: In Host :: SQL (or Settings :: SQL Console when you are using DNN 9.x) run the following script:

UPDATE {databaseOwner}{objectQualifier}Users SET IsDeleted = 1 WHERE IsSuperUSer = 0 AND Username NOT IN ('Admin', 'xyz')
GO

UPDATE {databaseOwner}{objectQualifier}UserPortals SET IsDeleted = 1 WHERE UserId IN (SELECT UserId FROM {databaseOwner}{objectQualifier}Users WHERE IsDeleted = 1)
GO

Please note: You do not want to delete the superusers (host users) and maybe some other users, please change the list at the end of the SQL statement accordingly, or remove the part in blue letters if not needed.

Then restart the application pool under Tools :: Restart Application (or Settings :: Server :: [Restart Application] button in DNN 9.x).

The users are now "soft-deleted", if you want to ultimately remove them you can do this under Admin :: User Accounts by clicking the "Remove Deleted Users" button. I have not found any way to do this in DNN 9.x, there seems to be no interface. So this SQL script might be helpful:

DELETE FROM {databaseOwner}{objectQualifier}Users WHERE IsDeleted = 1
GO

DELETE FROM aspnet_membership WHERE UserId NOT IN (SELECT UserId FROM aspnet_users WHERE Username IN (SELECT Username FROM {databaseOwner}{objectQualifier}Users))
GO

DELETE FROM aspnet_users WHERE Username NOT IN (SELECT Username FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}CoreMessaging_UserPreferences WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserAuthentication WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserPortals WHERE IsDeleted = 1
GO

DELETE FROM {databaseOwner}{objectQualifier}UserProfile WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRelationshipPreferences WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRelationships WHERE RelatedUserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRelationships WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRoles WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UsersOnline WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

Please note: Depending on the modules you installed there might be other tables that contain a reference to the UserId, e.g. threads and posts in a forum module, orders in a shop module and so on. This statement is only an example with no claim on completeness!

If anything went wrong with this you can restore your database backup, but don't blame me - it is all on your own risk :-)

Happy DNNing!
Michael


Michael Tobisch
DNN★MVP
dnn-Connect.org - The most vibrant community around the DNN-platform
 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Fastest / best way to remove large number of users from DNN website?Fastest / best way to remove large number of users from DNN website?


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

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

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. 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.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2018 by DNN Corp Legal | Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out