Bulk deletion of spam accounts

Bulk deletion of spam accounts

This question is answered

Is there an easy way to delete a whole bunch of spam accounts based on their domain?  I ran the stored proc "aspnet_Users_DeleteUsers".  But that broke the site.  Was I supposed to do something else as well?

Andrew Wong
Lead Community Server Developer

Verified Answer
All Replies
  • You'll need to call the cs_User_Delete stored procedure for each user.

  • I created a standalone  bulk deletion tool for Community Server 2007 (not sure for other versions) for one of my forum. May be useful for other CS based forum admins too. URL: kidoos.net/.../619.aspx

  • ninethsense

    I created a standalone  bulk deletion tool for Community Server 2007 (not sure for other versions) for one of my forum. May be useful for other CS based forum admins too. URL: kidoos.net/.../619.aspx

    Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you.

    May Santa bring you something too large too fit in your chimny, too expensive to sell, and too important too tell anyone about!

  • yahoo... we are getting spammed with bogus users signing up for our forum and I am getting so tired of it.

  • Thank you, but how do I identify the MS SQL Server and Database used by my Community Server 2.0 forum?

  • Egads.  I just figured out why I was running out of disk space.  183474 users.  Only 3 are valid.

    The program ninethsense posted works to delete small numbers.  I'm no SQL expert so I'd appreicate it if someone would post a SQL query that would run the cs_User_Delete stored procedure for all users except three (mine, one other, and anonymous)?

    Thanks in advance.

  • Figured it out.  I think. It's running now. I played around and tested the inner part of the loop a bunch and it appears to work.  I'll know for sure tomorrow when it finishes (or maybe in a week with 183k users to delete... <g>).  This is really the first time I've ever written T-SQL in anger...

    DECLARE mycursor CURSOR FOR

    SELECT     UserID, UserName

    FROM         cs_Users

    WHERE     ((UserName <> 'cek') AND (UserName <> 'charlie') AND (UserName <> 'Anonymous'))

    ORDER BY UserName DESC;

    OPEN mycursor;

    DECLARE @id INT, @name VARCHAR(80), @count INT;

    SET @count = 0;

    FETCH NEXT FROM mycursor INTO @id, @name

    WHILE @@FETCH_STATUS = 0 BEGIN

    PRINT 'Deleting ='+ISNULL(@name, 'NULL');

    EXEC dbo.cs_User_Delete @id, 'Anonymous';

    FETCH NEXT FROM mycursor INTO @id, @name

    SET @count = @count + 1;

    END

    PRINT 'Number of users deleted = ' + CONVERT(VARCHAR(20),ISNULL(@count,0));

    CLOSE mycursor

    DEALLOCATE mycursor

  • Yay! This T-SQL script worked. I took 15+ hours to run, but it did the job. Hope this helps someone else.