sides of march (Brian DeMarzo)

Thoughts on life, liberty, and information technology

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 response to “Remove anonymous users from ASP.Net Membership tables”

  1. notnope Avatar
    notnope

    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

    Like

    Reply

Leave a reply to notnope Cancel reply