One should always be aware that your site could be attacked by spam bots if you don’t take the proper precautions. The easiest and quickest way to help prevent 500+ spam users from joining your site is to configure the default options to make it harder for spam bots to create accounts. Please note that this can also reduce the number of registrations on your site due to the extra steps it takes to register.

1. Navigate to the Account Settings tab in the control panel (/controlpanel/membership/AccountSettings.aspx).
2. In the Registration Settings tab Set Account Activation to Email.
3. If you have a full time moderator then you can also set New user Moderation Level to User is Moderated.
4. Navigate to the Post Settings in the control panel (/controlpanel/Settings/AdvancedConfiguration.aspx).
5. Make sure Enable Anonymous Posting is set to No.
6. Navigate to the Username Filters in the control panel (/controlpanel/tools/UsernameFilter.aspx).
7. Add any words you think are necessary, like offensive words. I would also add words of medication, payment or buying keywords, and health.
8. Navigate to the Manage Spam Blocker in the control panel (controlpanel/tools/ManageSpamRules.aspx).
9. Enable User Creation IP Frequency and configure the minutes to be 1440. This will stop all the spammers from joining from the same IP.
10. Enable any rules you think would benefit your community.
11. Click Save.

If you came to this article because you already had your site taken over by spam users created by bots, then continue reading. Please note: before continuing please create a database backup and continue at your own risk. I am not liable for any harm that comes to your database or community after running the following query.

1. Navigate to Manage Users in the control panel (controlpanel/membership/membersearch.aspx?SelectedNavItem=BrowseMembers).

2. Next if you know the email address of the spam account in question then enter *a-spam-domain.com (replacing the domain name with the email domain in question). Now if this returns only the spam accounts in question continue, otherwise you will need to modify the search criteria until you see the list of spam accounts.

3. Open SQL Server Management Studio and connect to your community server database.

4. Now backup your community server database. Here is a how to guide.

5. Run the following query and make sure it returns the list of user accounts you want to remove. You may need to update this query and change the domain name from step 2.

   SELECT * FROM cs_users WHERE Email LIKE '%@a-spam-domain.com'
   

6. If you notice that you had a bunch of users join your site all from a certain domain then this TSQL script will work for you out of the box. Also if your spam bots have created posts, you may want to create a new user of which you want to assign the spam posts to. All you need to do is to create a new user account and change line 18 from Anonymous to the case sensitive user name you created. I modified the following query quite a bit to fit this scenario. Please note: you will need to update line 10 to match the like query in step 5 and also change the domain name.

   declare @Proc nvarchar(50)
   declare @RowCnt int
   declare @MaxRows int
   declare @ExecSql nvarchar(255)
   declare @user_id int
   select @RowCnt = 1
   declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , UserID int NOT NULL)
   insert into @Import (UserID) select UserID from cs_users where Email like '%@a-spam-domain.com'
   select @MaxRows = count(*) from @Import
   while @RowCnt <= @MaxRows
   begin
       select @user_id = UserID from @Import where rownum = @RowCnt
       execute [dbo].[cs_User_Delete] @user_id, 'Anonymous'
       Set @RowCnt = @RowCnt + 1
   end
   

7. Go back and run the query from step 5, If it returns no results then all the spam accounts have been removed.

Join the mailing list