Learn More





DNN Community Blog

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.

Quick tip: getting rid of spam users

After getting a couple of questions in the Forums and directly, I'd like to provide options for getting rid of spam users, which is cumbersome via UI, if thousands of registration have been added by a bot.

Unfortunately, it is not as easy as just deleting items from a single table, as user Information is distributed in multiple tables inside the database, therefore we need a .

You are in a comfortable situation, if your site is using verified or private registration - in this case all spam users are unauthorized and you simply need to hit "delete unauthorized users" in Admin > User Accounts.

In all other cases, you will need to identify the users to delete from the database and mark as "deleted", in order to use "remove deleted users" (hard-delete) those users from all tables.
If your site only has a host and admin user you would run the following statement inside Host > SQL (check "run as script" in all DNN versions prior to 7.2):

UPDATE {databaseOwner}[{objectQualifier}UserPortals] 
SET isDeleted = 1
 WHERE UserID NOT IN (SELECT UserID FROM  {databaseOwner}[{objectQualifier}Users 
WHERE isSuperuser = 1 OR UserName Like 'Admin')

If you may identify all users by registration date, e.g. since first of july, you would run

UPDATE {databaseOwner}[{objectQualifier}UserPortals] 
SET isDeleted = 1
 WHERE UserID NOT IN (SELECT UserID FROM  {databaseOwner}[{objectQualifier}Users] 
WHERE isSuperuser = 1 OR UserName Like 'Admin' or CreatedOnDate < 2014-07-01)

Of course, this will become more difficult, if regular users registered the same time. You may consider creating a role "verifiedUsers" and move all regular users manually into this group. Now the command would look like

UPDATE {databaseOwner}[{objectQualifier}UserPortals] 
SET isDeleted = 1 WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}[{objectQualifier}Users]
WHERE isSuperuser = 1 OR UserName Like 'Admin' or CreatedOnDate < 2014-07-01)
AND UserID NOT IN (SELECT UserID FROM {databaseOwner}[{objectQualifier}vw_UserRoles]
WHERE RoleName Like 'verifiedUsers')

Afterwards you may use "Remove Deleted Users" link in Admin > User Management, to delete all rows.

For DNN Versions prior to DNN 7.3.1, you need to delete user folders manually, which reside nested inside users subfolder of the portal folder. Unfortunately, it is difficult to identify all folders from deleted users, but you may delete all empty folders without subfolder and files inside, as the folder for a still existing user will be re-created, once the user logs in again for the first time. You may use the following command (sing a command line on the server, while being inside the users folder)

for /f "delims=" %i in ('dir /ad /s /b') do @rd "%i"

Finally, you should enter file manager in DNN Admin menu and perform a recursive sync to get rid of all folder entries and folder permissions in the database.



Mitchel Sellers
Thanks for posting this Sebastian! One note, if you have 50,000+ users it is quite possible that "Remove Deleted Users" will bring the site to a 500 error and you might need to try this multiple times. But it WILL cleanly remove the users.
Mitchel Sellers Thursday, August 14, 2014 6:11 PM (link)
Sebastian Leupold
I used this method today to remove 44k users from a site (DNN 6.2) and while hard deleting all took more than an hour it executed constantly without need to restart (I frequently checked the remaining number in Host > SQL, using my old trick opening a 2nd browser window ;))
Sebastian Leupold Friday, August 15, 2014 3:25 AM (link)
Michael Tobisch

for deleting the User Directories I wrote a little SQL script that outputs a set of commands, these can be copied to a batch file and run from inside the "Users" folder. It does not take multi-portal environments into account, so it has to be adapted for this purpose. Also a database owner/object qualifier has to be used if necessary, you can't run the script from Host :: SQL as it uses the PRINT command from SQL:


DECLARE @i int = 1

DECLARE @AllPossibleUserIDs table (UserID int)
DECLARE @MissingUserIDs table (UserID int)

WHILE (@i <= @MaxUserID)
INSERT INTO @AllPossibleUserIDs(UserID) VALUES(@i)
SET @i = @i + 1


DECLARE @MissingUserID int
OPEN CurMissingUserIDs
FETCH NEXT FROM CurMissingUserIDs INTO @MissingUserID
PRINT 'RD ' + CAST(@MissingUserID AS nvarchar(max)) + ' /S /Q'
FETCH NEXT FROM CurMissingUserIDs INTO @MissingUserID

CLOSE CurMissingUserIDs


Happy DNNing!
Michael Tobisch Monday, August 18, 2014 4:45 AM (link)
Sebastian Leupold
thanks for the script, it does remove non-empty user folders for deleted users as well, while mine just removes all empty folders (expecting each to be re-created, when the owning user logs in the next time.
Sebastian Leupold Monday, August 18, 2014 4:52 AM (link)
Sebastian Leupold
the following version of Michael's script might be executed from Host > SQL:

DECLARE @MaxUserID int = IDENT_CURRENT('|databaseOwner}[{objectQualifer}Users]');
DECLARE @i int = 1;
DECLARE @MissingUserIDs table (UserID int);

-- generate tally table:
WHILE (@i <= @MaxUserID) BEGIN
INSERT INTO @MissingUserIDs(UserID) VALUES(@i);
SET @i += 1;
-- remove existing records:
DELETE FROM @MissingUserIDs WHERE UserID IN (SELECT UserID FROM |databaseOwner}[{objectQualifer}Users]);

-- return records:
SELECT 'RD ' + CAST(UserID AS nvarchar(max)) + ' /S /Q' AS run_in_Windows FROM @MissingUserIDs;

Sebastian Leupold Monday, August 18, 2014 7:20 AM (link)
Sebastian Leupold
I just released a script, which creates and executes a procedure to finally remove ("hard delete") users, which are marked as deleted ("soft delete"). Supports to specify a single or all portals.
This prevents timeouts, if you are using "Remove deleted users" inside DNN with thousands of users.
Download the free script from
Sebastian Leupold Wednesday, September 10, 2014 12:46 PM (link)
David Poindexter
Hey everybody, I thought I would just share a great tool we found to delete the empty user directories. It is a well thought out Windows app and you can find it at and on GitHub if you are interested in delving into the source code. This may provide a bit more "warm fuzzies" for those less comfortable with the command line and SQL options mentioned here. Plus, there is a nice log that gets generated and can be shared with clients as appropriate. In addition to the various preventative measures, the Bulk User Manager v3.3 module combined with this tool have really helped us with our many client sites affected by this spam user annoyance. Cheers!
David Poindexter Friday, September 12, 2014 4:44 PM (link)
Bruce Chapman
I have been building and testing a more thorough solution to this - I have posted it on GitHub It uses a separate Windows application to submit requests through to the server and you can set and forget while it chews away and removes the user records and folders properly and cleanly. It's designed to slowly work away so you don't kill your server trying to delete a massive batch of users all at once, and logs out everything that was deleted.
Bruce Chapman Friday, September 19, 2014 1:28 AM (link)
Sebastian Leupold
Nice job, Bruce!
Sebastian Leupold Friday, September 19, 2014 3:29 AM (link)
David Poindexter
Excellent @Bruce!
David Poindexter Friday, September 19, 2014 2:03 PM (link)
Bruce Chapman
I just used it to clean out 84,000 spam registered users. The process took 14 hours and didn't impact the site performance at all. All the user folders, user records and asp net user records are gone. I would appreciate others trying it out and suggesting improvements so that it can be a community tool.
Bruce Chapman Tuesday, September 23, 2014 9:06 AM (link)
Ryan Moore
What a great conversation back and forth here and some excellent scripts and tools. Makes me lament that I only have a handful of sites with perhaps 6 or 10K in spam user records to clean up with Bruce's new tool !
Ryan Moore Friday, October 03, 2014 1:59 AM (link)
Geoff Tripoli
So glad I found Bruce's tool. Nice work Bruce!

Any ideas for new features? I might have some time to contribute to it.
Geoff Tripoli Wednesday, January 14, 2015 1:38 AM (link)
Franco Prati
Hi, nice job. I would like to use it too, but how does the script discriminate between a valid user and a spam user? I see it is discussed on Bruce tool page, but I could not see how it does it. I am afraid of just deleting the valid ones... Can somebody clarify, please?
Franco Prati Monday, February 02, 2015 11:04 AM (link)
Bruce Chapman
@Franco the tool I built deletes any 'soft deleted' user, with a 'IsDeleted' flag set to 1. I have also created some scripts to try and identify those users. You can use a date range, missing last IP address or anything, really, to identify which users are invalid spam users. Set the IsDeleted flag to 1 (in the PortalUsers table) and then remove them as you need.
Bruce Chapman Thursday, February 19, 2015 4:33 PM (link)
Sebastian Leupold
Franco, you need to adopt my script accordingly to identify the spam users - I simply assumed, all users, who registered before a given date are valid:
CreatedOnDate < 2014-07-01
the other option would be excluding all users in a specific role. if you are able to identify users by IP range, you may use it as a filter as well. Unfortunately, there is no rule to identify all spam users automatically, they still refuse to use spam as surname ;)
Sebastian Leupold Thursday, February 19, 2015 7:29 PM (link)
Bruce Chapman
@Franco - also check my blog post for other ways of identifying spam users by queries. There's a couple of different ways you can figure it out.
Bruce Chapman Thursday, February 19, 2015 9:01 PM (link)
Franco Prati
Hi, thank you all, now I get the concept. I then proceeded to set the isdeleted to a number of users via host sql. I can clearly see with host-sql and SSMS that the flag is set on those records.
However, the admin-user account 'remove deleted users' has no effect and those users are not 'flagged' deleted. I restarted dnn, cleared cache, etc., to no help. The users tableshows those users with isdeleted=1 and admin user account does not see them as such.
Running dnn 7.3.4. And just one portal, 0.
Any suggestion?
Franco Prati Friday, February 20, 2015 4:16 PM (link)
Don Wood
Bruce's tool looks like a great tool to do exactly what I need. However, I can't get it to work. I'm getting this message in the log:

Finished Anonymous Server Ping at 11:28 AM; Elapsed time : 0.2060117 seconds
Call to http: // resulted in 500 Server Error. Check Error field.

Any clue as to what the problem might be?

Don Wood Saturday, February 21, 2015 12:54 PM (link)
Sebastian Leupold
Franco, did you use proper PortalID?
Sebastian Leupold Sunday, February 22, 2015 6:20 PM (link)
Sebastian Leupold
sorry, I cannot comment on Bruce's tool, I haven't had a look at it.
Sebastian Leupold Sunday, February 22, 2015 6:21 PM (link)
Bruce Chapman
@franco - you must set the UserPortal record, not the User record (for the IsDeleted flag).

The 500 error might be related to your password retrieval settings for host users. If you look at the github page there is a discussion around this. If you can use the 'ping' ok in anonymous but it doesn't work for host this might be the problem.
Bruce Chapman Sunday, February 22, 2015 11:17 PM (link)
Franco Prati
Oh right, I missed the fact we have 2 tables, users and userportals. All fixed now. Thanks a lot to Sebastian and Bruce.
Franco Prati Monday, February 23, 2015 4:01 AM (link)
Don Wood
Can anyone tell me what files should be in the DesktopModules/Dnn_BulkUserDelete/ directory? All I can see are a folder with SqlDataProvider folder with couple of SQLDATAPROVIDER files.
Don Wood Monday, February 23, 2015 2:36 PM (link)
Sebastian Leupold
Don, did you install the module using Extension Install Wizard in Host > Extensions, after unblocking the module package?
Sebastian Leupold Monday, February 23, 2015 2:50 PM (link)
Don Wood
I installed using Host>Extensions. I unzipped the package that I downloaded which included both the module and the console. I installed the package:
Don Wood Monday, February 23, 2015 2:58 PM (link)
Sebastian Leupold
oops, my fault. the module is just providing a web service.
UI is the Windows application.
Sebastian Leupold Monday, February 23, 2015 3:34 PM (link)
Don Wood
Yes. The error message that I'm getting: Finished Anonymous Server Ping at 2:36 PM; Elapsed time : 0.0810046 seconds
Call to http: // resulted in 500 Server Error. Check Error field.

I don't see on the server anything other than the SQLDATAPROVIDER files in DesktopModules/DNN_BulkUserDelet/SqlDataProvider directory and no other files or folders in Dnn_BulkUserDelete
Don Wood Monday, February 23, 2015 3:39 PM (link)
Sebastian Leupold
it sounds like a bug, but I am unable to identify the cause
Sebastian Leupold Monday, February 23, 2015 4:20 PM (link)
Don Wood
Tried installing it on a localhost site and it seems to work ok there. And the files configuration seem very similar.
Don Wood Tuesday, February 24, 2015 12:47 PM (link)
Bruce Chapman
If you're having problems with the Bulk User Delete tool, please log them at the github project page where the tool is available.
Bruce Chapman Tuesday, February 24, 2015 6:54 PM (link)
Don Wood
Thank you. I understand.
Don Wood Wednesday, February 25, 2015 2:26 PM (link)
Thank you for great post on this Spam bot problem.

I have a question, i am running some sites on an older DNN version (6.2). Can i run the script on this version?

tobias.l Monday, March 09, 2015 9:50 AM (link)
Sebastian Leupold
the script hasn't been tested against previous versions, it might run but issue Errors due to missing tables.
Don't Forget to start with a database backup!
Sebastian Leupold Tuesday, March 10, 2015 4:19 AM (link)

Comment Form

Only registered users may post comments.


2sic Daniel Mettler (125)
Aderson Oliveira (15)
Alec Whittington (11)
Alex Shirley (10)
Andrew Nurse (30)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (22)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (203)
Chris Paterra (55)
Clinton Patterson (28)
Cuong Dang (21)
Daniel Bartholomew (2)
Dave Buckner (2)
David Poindexter (3)
David Rodriguez (2)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (74)
Geoff Barlow (6)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (270)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matthias Schlomann (15)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Mike Horton (19)
Mitchel Sellers (28)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Peter Donker (52)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott S (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (2)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (37)
Will Strohl (163)
William Severance (5)
Try Evoq
For Free
Start Free Trial
a Demo
See Evoq Live
Need More Information?