Forum

Hi Aderson

many thanks for the script - all worked great.

I also came across this to delete empty user folders:

for /f "delims=" %d in ('dir /s /b /ad ^| sort /r') do rd "%d"

Source https://superuser.com/questions/39674/recursively-delete-empty-directories-in-windows

Thanks again

 

FYI, I found this thread very helpful in reducing our spam accounts. I modified the scripts though to make them a bit more robust in wrapping all the transactions related to a user into a single transaction, so if one fails, that user account is not corrupted. This could be put into a stored procedure easily enough. Note this uses the presumption that you used another script to update the UserPortals.IsDeleted to 1 for those that you feel need to be deleted.

 

DECLARE @i int
DECLARE @user_id int
DECLARE @numrows int
DECLARE @userToDelete_table TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , UserId int
)

SET NOCOUNT ON

-- populate user table in-memory table to hold distinct user id
INSERT @userToDelete_table
Select Distinct UserId
From UserPortals 
WHERE PortalID = 1 and IsDeleted = 1

-- enumerate the table

SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @userToDelete_table)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM @userToDelete_table))
	--WHILE (@i <= 1000)
    BEGIN
        -- get the next primary key
        SET @user_id = (SELECT UserId FROM @userToDelete_table WHERE idx = @i)
		
		--
		BEGIN TRAN
			PRINT('******************************************')
			PRINT('UserId to Delete: ' + CAST(@user_id as varchar(10)))

			DELETE UserRoles WHERE UserID = @user_id
			PRINT('UserRoles Deleted for User ID: ' + CAST(@user_id as varchar(10)) + '. Rows Deleted: ' + CAST(@@ROWCOUNT as varchar(10)))
			
			DELETE UserPortals WHERE UserID = @user_id
			PRINT('UserPortals Deleted for User ID: ' + CAST(@user_id as varchar(10)) + '. Rows Deleted: ' + CAST(@@ROWCOUNT as varchar(10)))

			DELETE userprofile WHERE UserID = @user_id
			PRINT('userprofile Deleted for User ID: ' + CAST(@user_id as varchar(10)) + '. Rows Deleted: ' + CAST(@@ROWCOUNT as varchar(10)))
			
			DELETE aspnet_membership WHERE UserId = 
				(
					SELECT au.UserId 
					FROM dbo.aspnet_Users as au INNER JOIN
                         dbo.Users as u ON au.UserName = u.Username
					WHERE  u.UserID = @user_id
				)
			PRINT('aspnet_membership Deleted for User ID: ' + CAST(@user_id as varchar(10)) + '. Rows Deleted: ' + CAST(@@ROWCOUNT as varchar(10)))
			
			DELETE aspnet_users WHERE UserId = 
				(
					SELECT au.UserId 
					FROM dbo.aspnet_Users as au INNER JOIN
                         dbo.Users as u ON au.UserName = u.Username
					WHERE  u.UserID = @user_id
				)
			PRINT('aspnet_users Deleted for User ID: ' + CAST(@user_id as varchar(10)) + '. Rows Deleted: ' + CAST(@@ROWCOUNT as varchar(10)))
			
			DELETE Users WHERE UserID = @user_id
			PRINT('Users Deleted for User ID: ' + CAST(@user_id as varchar(10)) + '. Rows Deleted: ' + CAST(@@ROWCOUNT as varchar(10)))

			PRINT('')
			PRINT('')

		COMMIT    

        -- increment counter for next employee
        SET @i = @i + 1
    END