Home
 
     

How to delete spam users DNN

May 15 2015

How to delete spam users DNN

There are crazy people that use a bot to register lots of spam users in your dotnetnuke website. It doesn’t matter if you enable captcha or not because they can easily get passed that. So if one day you woke up and found out that you got four thousand new users in your website over a night do not get supper exited! It is probably the crazy guys having fun (really how it is even funny, I don’t get their humor I guess!)

This fake users basically cannot do anything in a standard dnn installation that has only company website functionality (as long as I know). Nevertheless it can really become a headache when some of their emails are associated with spam filters, so called Honeypots method. Your server sends that email address an automatic welcome message, and then bang you are in the trap without you even know! Your IP get associated to spamming or your web hosting kicks you out!

Well, I have no easy suggestion how to prevent this when you are running a website that registration is public, but if you do not need public registration, then just disable it!

 

From Admin page click on SITE SETTINGS then select tab User Account Settings

Under  Registration Settings  you should see

User Registration with four possible alternative

 None                 Private               Public                 Verified

Take None if you are under attack and/or you do not need new users.

 

Then probably you want to delete all the fake users from your database.  Today I removed more than 5000 of these users for a client, so I am sharing it so you don’t need to spent a couple of hours figuring it out the right way.

Then I have to emphasis that this solution is “as is” and I take no responsibility if led to a catastrophe or something… LOL !

But seriously BACK UP YOUR DATABASE FIRST!!!!!

To delete users you going to need some kind of tool to execute query on your database, you can use SQL management studio, or you can even use your DNN tool:

Host > SQL   

Run the script below in the right database and you should get rid of all spam users.  


* note that we delete 5000 users at a time. It  should take about 1 min for the process to be executed. In case you have 5000 spam users, you have more you should run the script again and again until there is no more spam users left.


 

select  top 5000 [UserId]
into #ControlTable 
from [dbo].[Users]
 where (LastIPAddress is null or LastIPAddress ='' )


declare @userID int 
declare @userName nvarchar (200)
declare @ASPUserID uniqueidentifier

while exists (select * from #ControlTable)
begin

    select top 1 @userID = UserID
    from #ControlTable
    order by userID asc

	select  @userName=UserName  from [dbo].[Users] where UserID = @userID
	select  @ASPUserID = userID from [dbo].[aspnet_Users] where UserName = @userName

	delete from [dbo].[aspnet_Membership] where userID = @ASPUserID

	delete from [dbo].[aspnet_Users] where UserName = @userName

	delete from [dbo].[Profile] where UserID = @userID

	delete from [dbo].[UserPortals] where UserID = @userID

	delete from [dbo].[UserProfile] where UserID = @userID

	delete from [dbo].[Users] where UserID = @userID


    delete #ControlTable
    where UserID = @userID

end

drop table #ControlTable

Privacy Statement Terms Of Use Copyright 2011 by Rooznamechi