Resetting / deleting all the users in aspnet_* tables

Hi,

How can i reset to zero, deleting all the users who are in the aspnet_* tables in my production 2003 server?

Thanks

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

|||

Assuming you don't have any other tables in the database that have foreign key constraints to the aspnet_* tables, you could simply rerun the T-SQL scripts that create those tables (since those scripts first drop the tables, if they exist). You can find the T-SQL files in %WINDOWS%\Microsoft.NET\Framework\v2.0.50727 or you can execute them using the aspnet_regsql.exe command line tool.

UPDATE: To provide more clarity... the T-SQL scripts in that folder are named like Install*.sql. InstallMembership.sql, for example, contains the scripts for the membership-related tables; InstallRoles.sql for the roles. InstallCommon.sql has them all.

|||

Sweeperq:

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

The only difficulty with this approach is since the aspnet_* tables have a number of foreign key constraints among themselves, if there is data in the tables you have to truncate (drop) the tables in the correct order, otherwise you will get foreign key constraint errors and the truncate will fail.

|||

So TRUNCATE does not trigger cascading deletes?

|||

The stored procedure aspnet_users_deleteuser takes care of the relationships and constraints - it's what the Membership.DeleteUser method itself calls. You can call it manually. Here's anarticle on deleting users from membership using the stored procedure.

But this deletes one user at a time. You want to delete all users? You can use SQL to cursor through the aspnet_users table, calling the stored procedure each time.

Otherwise, you need to truncate in the correct order which would be this: aspnet_usersinroles, aspnet_profile, aspnet_personalizationperuser, aspnet_membership, aspnet_users.

|||

Sweeperq:

So TRUNCATE does not trigger cascading deletes?

It does, if I'm not mistaken. Problem is, the foreign key constraints on the aspnet_* tables do not cascade deletes. So you have to manually delete the "child" tables first before you can delete the data from the parent table.