How to delete old aspnet users with aspnet_Users_DeleteUser procedure?

Question

I am using the standard aspnet membership tables and I'm doing maintenance of our database. We want to remove all the users that have not logged in for a year.

I know how to call the aspnet_Users_DeleteUser stored procedure in order to delete a single user, but how can I use it to delete all users where aspnet_Users.LastActivityDate < '1/1/2015'?

I can write a SELECT statement to get out the usernames to delete, but how can I execute the procedure on each of them?

Answer 1

This should do the trick...

Declare @ApplicationName  nvarchar(256),
        @UserName         nvarchar(256),
        @TablesToDeleteFrom int = 15, 
        @NumTablesDeletedFrom int

Declare cur 
Cursor  For
Select  aa.LoweredApplicationName,
        au.LoweredUserName
From    [dbo].[aspnet_Users] au
Join    [dbo].[aspnet_Applications] aa
        On  au.ApplicationId = aa.ApplicationId
Where   LastActivityDate < '1/1/2015'

Open    cur

Fetch   Next
From    cur
Into    @ApplicationName,
        @UserName

While   @@FETCH_STATUS = 0
Begin
        Exec    [dbo].[aspnet_Users_DeleteUser] 
                    @ApplicationName,
                    @UserName,
                    @TablesToDeleteFrom,
                    @NumTablesDeletedFrom Out

        Fetch   Next
        From    cur
        Into    @ApplicationName,
                @UserName
End

Close   cur
Deallocate cur