Remove anonymous users from ASP.Net Membership tables

If you use the ASP.Net membership tools, have <anonymousIdentification enabled="true" /> specified in your Web.config, and get lots of anonymous visitors, it’s only a matter of time before your database grows. What’s filling it up is the countless user records for your anonymous users.

If you don’t need to track user and profile information for an anonymous user once they leave the site, you can delete the unneeded data by running a SQL script. The following script will delete from your membership tables all anonymous users whose last activity was more than 7 days ago.

delete from aspnet_profile
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_usersinroles
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_membership
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_personalizationperuser
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_users
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

Doing this may be very important to those using a shared hosting plan that has limited SQL disk space, as those anonymous users can quickly eat up disk space. Since each row in aspnet_users takes up just over 1kb, having 1,000 anonymous users will eat up 1MB — something that adds up quick if you only have 250MB of SQL disk storage.

One thought on “Remove anonymous users from ASP.Net Membership tables”

  • SQL-PROCEDURE to remove all anonymous users :

    CREATE PROCEDURE dbo.RemoveAnonymousUsers
    AS
    –temporary table to stock anonymous users ids
    CREATE TABLE #AnonymousUsers(
    Id uniqueidentifier,
    UserName NVARCHAR(256)
    )
    –get all Anonymous
    INSERT INTO #AnonymousUsers SELECT UserId,UserName FROM aspnet_Users where IsAnonymous = ‘True’

    –useless, here only to display anonymous users list (last time you will see them)
    SELECT * FROM #AnonymousUsers

    DECLARE @UserSystemName uniqueidentifier

    DECLARE cursor_categories CURSOR FOR
    SELECT Id FROM #AnonymousUsers

    OPEN cursor_categories
    FETCH cursor_categories INTO @UserSystemName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    –remove time
    DELETE FROM aspnet_Profile WHERE UserId = @UserSystemName
    DELETE FROM aspnet_UsersInRoles WHERE UserId = @UserSystemName
    DELETE FROM aspnet_Membership WHERE UserId = @UserSystemName
    DELETE FROM aspnet_PersonalizationPerUser WHERE UserId = @UserSystemName
    DELETE FROM aspnet_Users WHERE UserId = @UserSystemName
    ————————-
    FETCH cursor_categories INTO @UserSystemName

    END

    CLOSE cursor_categories
    DEALLOCATE cursor_categories

    RETURN 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>