I have used this type of query to find inactive users or in my terms users who have created an account and not logged in at all or logged once and not since:
SELECT *
FROM Users INNER JOIN
aspnet_Membership INNER JOIN
aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId ON Users.Username = aspnet_Users.UserName INNER JOIN
UserPortals AS UserPortals_1 ON Users.UserID = UserPortals_1.UserId
WHERE DATEDIFF(mi, aspnet_Membership.CreateDate, aspnet_Users.LastActivityDate) <5 AND Users.UserId<>4
ORDER BY LastLoginDate desc
Note that part on red above. There might be some userid's that you don't want to mark unauthorized so list all of those there.
What this does it finds all users who have created an account and not done anything or done something within 5 minutes after that and after that have had no activity. You can change this number to something else as well.
After verifying that these are the users I want to remove, I have enhanced the query to:
UPDATE UserPortals
SET Authorised = 0
WHERE (UserId IN
(SELECT DISTINCT UserPortals_1.UserId
FROM Users INNER JOIN
aspnet_Membership INNER JOIN
aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId ON Users.Username = aspnet_Users.UserName INNER JOIN
UserPortals AS UserPortals_1 ON Users.UserID = UserPortals_1.UserId
WHERE DATEDIFF(mi, aspnet_Membership.CreateDate, aspnet_Users.LastActivityDate) <5)) AND UserId<>4
Changed the SELECT query a bit (yellow part) not to hit too many times same user.
After executing UPDATE statement your inactive users will be marked as Unauthorized and you can review those once more in DNN user accounts management UI and delete those from there. That will take care of that everything will be cleared correctly from the database.
Before running the UPDATE statement verify the SELECT part that correct users are really selected.
Furthermore, you can also enhance SELECT part to include some date range like
AND (aspnet_Users.LastActivityDate < CONVERT(DATETIME, '2010-12-31 23:53:00', 120) AND aspnet_Users.LastActivityDate > CONVERT(DATETIME, '2010-10-30 23:53:00', 120))
Just pick your favorite date & times on above.