I use YetAnotherForum (YAF) for my Forums here. Like others, I get hundreds of spam users created each month. I wrote the following SQL to delete any user that hasn’t visited in the last day, never posted or emailed a message, isn’t the last user on a forum, and isn’t in the Active list.
-- first delete the UserGroup rows.
DELETE FROM dbo.yaf_UserGroup
WHERE UserID in
(SELECT u.UserID
FROM dbo.yaf_User u
WHERE u.LastVisit < getdate() -1
AND u.NumPosts = 0)
AND UserID NOT IN
(SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(SELECT m.UserId
FROM dbo.yaf_Message m)
--
-- now delete the User rows.
--
DELETE FROM dbo.yaf_User
WHERE LastVisit < getdate() -1
AND NumPosts = 0
AND UserID NOT IN
(SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(SELECT m.UserId
FROM dbo.yaf_Message m)