Remove anonymous users from ASP.Net Membership tables

Posted on August 7, 2008 | by brian

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”

notnope says:

December 21, 2011 at 4:53 am

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

Reply

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

notnope says:

Leave a Reply Cancel Reply