The .NET Membership Provider provides us with a stored procedure ‘aspnet_Users_DeleteUser’ to delete a user from a particular application. There are problems with this. What if you have a user who is allocated to many applications? What if you need to delete more than one user? I came up with the following script to completely remove all users (except the admin) from all applications.

DECLARE user_cursor CURSOR FOR
SELECT ApplicationName AS field1, Username AS field2, UserId AS field3
FROM aspnet_Users CROSS JOIN dbo.aspnet_Applications
WHERE username != 'admin'

OPEN user_cursor
DECLARE @field1 NVARCHAR(256)
DECLARE @field2 NVARCHAR(256)
DECLARE @field3 UNIQUEIDENTIFIER

FETCH NEXT FROM user_cursor
	INTO @field1, @field2, @field3
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    PRINT @field1 + ':' + @field2
	    DELETE FROM dbo.aspnet_UsersInRoles WHERE UserId = @field3
		EXEC dbo.aspnet_Users_DeleteUser @ApplicationName = @field1,
		    @UserName = @field2,
		    @TablesToDeleteFrom = 50,
		    @NumTablesDeletedFrom = NULL
		DELETE FROM dbo.aspnet_Users WHERE UserId = @field3
		FETCH NEXT FROM user_cursor
		INTO @field1, @field2, @field3
	END
DEALLOCATE user_cursor;

The script works by first cross-joining applications and users, giving us a full list of every user to every application. We then use a cursor to loop the given set, and run our deletes against the set. This saved me countless hours of manual labor.