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 ...Batch Deleting UsersBatch Deleting Users
Previous
 
Next
New Post
9/8/2014 9:44 AM
 

Hello- 

Recently, our server has been hit HARD by the user registration bots, and is causing SERIOUS database bloat, which subsequently is causing performance issues server wide. In some cases, we have sites with over 30,000 users. I've since disabled the user registrations on all of our sites, but now I am left with the problem of deleting all these fake user accounts / profiles. 

I've tried using the script 

"update UserPortals

Set Authorised='False' where Authorised='True'"

and then "deleting unauthorized users", but on the site I tried that on, it completely crashed the whole server. (i attempted to delete around 1500 users at once, which I imagine was too much of a strain on the server) 

Can anyone help me figure out how I can delete the users in a different way? Is there an SQL script I can run that will delete unauthorized users from the server side, rather than from the front end DNN side? Would this help? 

Any advice on getting rid of massive amounts of users / profiles on our server would be greatly appreciated. 

 
New Post
9/8/2014 12:34 PM
 
Hard deleting 1500 soft deleted users should not crash your server - there seem to sth not correct with your DNN installation.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
9/8/2014 2:07 PM
 

I didn't think so- We are supposed to have managed servers, but they claim this does not fall into their services. I do know that they install the initial version of DNN and we just develop the skins and front end build.  

Just for my own understanding, is there an SQL command we could run that would mass delete unauthorized users (or just users in general) ? 

BTW Sebastian, I wanted to personally thank you for your help- you seem to always be the guy who replies to my posts with helpful tips and fixes. You definitely know our stuff :)

 

 
New Post
9/8/2014 5:23 PM
 
Had the same problem - I've fixed many client sites with this script I found on an earlier thread.
Run it in HOST > SQL
Be careful!

Three notes:
I found it optimal to delete about 2,000 users at a time.
The script may time out but just keep hitting run and it will eventually finish (on average I have to hit run 3 times to complete the query).
When all completed - you still have to delete the user folders created by the bogus user registrations.

Copy from below here to the end and replace the user ID numbers with the user ID numbers from your registrations in each of the queries.


DELETE [UserRoles]
WHERE UserID >= 30001 AND UserID <=32000

DELETE {databaseOwner}[{objectQualifier}UserPortals]
WHERE UserID >= 30001 AND UserID <=32000

DELETE {databaseOwner}[{objectQualifier}UserProfile]
WHERE UserID >= 30001 AND UserID <=32000

DELETE {databaseOwner}[{objectQualifier}aspnet_Membership] WHERE UserId IN (SELECT UserId FROM {databaseOwner}[{objectQualifier}aspnet_Users] WHERE UserName IN (SELECT UserName FROM {databaseOwner}[{objectQualifier}Users]
WHERE UserID >= 30001AND UserID <=32000
))
DELETE {databaseOwner}[{objectQualifier}aspnet_Users] WHERE UserName IN (SELECT UserName FROM {databaseOwner}[{objectQualifier}Users]
WHERE UserID >= 30001AND UserID <=32000
)
DELETE {databaseOwner}[{objectQualifier}Users]
WHERE UserID >= 30001AND UserID <=32000
 
New Post
9/8/2014 8:16 PM
 
Findeight, thanks, but if your server crashes, there is an issue with configuration (DNN has issues for sure, but they are usually sandboxed within ASP.Net).
Hard deleting users does affect a number of tables (some incorporated by a membership provider) and 3rd party developers might have placed FKs on the users table (although they are not supposed to), therefore there is no SQL script for it.

If you are facing issue with the database, please try to solve it first, before applying the script. Let me know, if you need any assistance (contact leupold at dnnwerk.de, please don't use messaging on this website)

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Batch Deleting UsersBatch Deleting Users


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 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out