Deleting Users from Membership Tables with SQL

Summary

During early phases of your Membership Web Site you will probably create many test users to verify your new application. As you start getting "real" members, you may not want the test members to clutter up the .Net security database.

This article looks at removing users by logging into the database and running SQL directly, using the same method as the .Net membership provider. The article provides two sample usages of the SQL syntax to remove members.

Test Users on the Membership Site

During early phases of your Membership Web Site you will probably create many new test users to verify your new application. Are your "Administrator" users able to access all the right pages to manage your site? Are the users assigned to the "Friends" role blocked from the administration pages?

When you've finished verifying the functionality, chances are you'll have a whole load of test users who have "registered" with the site. You may have even got creative with the names - mickeymouse, donaldduck etc with the appropriate email addresses at disney.com!

Programmatically or by SQL

ASP.NET pages can programmatically delete users using methods of the membership provider.

The Membership.DeleteUser method calls a stored procedure in the ASP.NET database to remove the user. The stored procedure is called aspnet_Users_DeleteUser. In this article we look at calling the stored procedure directly by logging into the database and running SQL.

The User Tables

To understand usage of the stored procedure, it's important to be aware that there are a number of tables in the ASP.NET database that contain data associated with a user.

One of the tables is aspnet_Membership - this table contains the user's email, password, password recovery details etc. Unless you explicitly state otherwise, the default behaviour of the stored procedure is only to remove the user from aspnet_membership.

For example, unless specified the user will not be removed from associated roles - the user-to-role relationship is mapped in the aspnet_UsersInRoles table. If you are using user profiles in your application, the deleted user by default will be left in the aspnet_Profile table.

Readers familiar with database design may wonder why a User can remain in the associated tables and be removed from the aspnet_membership table. As it happens, aspnet_membership is not the main parent table (otherwise a User could not be deleted from it and remain in child tables). The parent table is a table called aspnet_Users - and the default deletion behaviour keeps the data in that table.

Using the Delete Stored Procedure

The stored procedure aspnet_users_DeleteUser accepts four parameters.

The first parameter is @ApplicationName. Unless you have set up a specific application name, use "/".

The second parameter is @UserName.

The third parameter is @TablesToDeleteFrom, an integer parameter that confuses many developers. This integer represents a bitmask that specifies the precise tables from which you want to remove the user. Don't worry if you don't understand bit masks, there are two simple rules to follow that will suit the vast majority of cases - pass in a value of 1 or 15.

The fourth parameter is @NumTablesDeletedFrom, an output parameter. You don't pass in a value to this parameter, instead you specify an integer variable which is populated by the stored procedure. It will tell how many tables that the user was removed from.

Sample Usage: Deleting the User while Leaving an Archive

You may be wondering why you would want to remove the user from aspnet_Membership while leaving all associated data in the other user tables. When your application is live, think carefully about "blowing away" all user data - as we show in the next section. Depending on the nature of your application and your business, you may have legal requirements to keep a history of people who signed up to your web site.

If in doubt of your obligations (and the need to protect your back!), use this procedure call:

declare @numTablesDeletedFrom int

 

exec aspnet_Users_DeleteUser '/', 'joebloggs', 1, @numTablesDeletedFrom OUTPUT

 

select @numTablesDeletedFrom

 

In this piece of SQL code, we declare an integer variable to hold the one output parameter from the stored procedure. Then the exec statement calls the stored procedure, passing in the required parameters.

The procedure is passed '/' for the Application Name, 'joebloggs' as our sample User Name and a 1 to indicate that only the aspnet_membership table is to be affected.

The select statement is simply to display the number of tables that were removed. You should see a value of 1 displayed. If a value of 0 is displayed, then either the UserName is incorrect or you have a specific Application Name.

Sample Usage: Blowing Away All User Details

Test users obviously don't need to be kept cluttering up your database. This sample procedure call passes in a value of 15 to remove the user from all the associated user tables.

This procedure call is for a sample User Name of 'testuser' and '/' Application Name.

declare @numTablesDeletedFrom int

 

exec aspnet_Users_DeleteUser '/', 'testuser', 15, @numTablesDeletedFrom OUTPUT

 

select @numTablesDeletedFrom

If you're using a simple membership model of users and roles (no profiles or personalization), you'll generally see @numTablesDeletedFrom populated with 3.

You'll see 2 if the user hasn't been assigned to a role yet (aspnet_membership and aspnet_users are the minimum number of tables that are populated.

If a value of 0 is displayed, then either the UserName is incorrect or you have a specific Application Name.

List of User Tables

If you're curious about which .Net membership tables may have user data, here is the list:

Caution: Extending Membership with New Tables

The aspnet_users_DeleteUser is specific to the .Net membership tables. If you are simply using .Net security, then this procedure is all you need.

Some developers may choose to add additional tables to hold user data specific to their application. In particular, if the standard .Net profile usage doesn't meet your requirements you may wish to add tables that contain the UserID and additional data. Proper database design will make the new table a child of aspnet_users - and this of course will mean that the standard deletion procedure will not be able to run as a "full" delete, because there may be data left in the new table.

Even if you yourself haven't specifically designed new user tables, you may have installed an application that does. For example, Community Server has additional user tables that are children of aspnet_users.

It is not difficult to incorporate the additional user tables in a deletion routine. Bear in mind that the standard aspnet_users_DeleteUser wraps the multiple table deletions within a single transaction: this means that if any one deletion fails (or a connection is lost midway through the procedure), any succesful deletions will be reversed. This is exactly what is required - we don't want "half-way" deletions. For this reason, the author recommends that when creating a new deletion process that the standard procedure is copied and the new tables are incorporated within the main transaction.

Summary

This article provided two sample usages of the SQL syntax to delete members from the a .Net security application. There are several tables that store user data in the .Net data model, and these tables were explored briefly.

Submitted: 21 June 2007

Author: Margaret Cruise O'Brien

(c) M.C.O.B. Technology 2007