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.
ALWAYS START WITH A DATABASE BACKUP!!
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.

HTH