How To Delete Site Member From ASP.NET Membership

ASP.NET provides a complete system for user registration, authentication and authorization on web site. To delete user, we can use DeleteUser method of Membership class. Code implementation could look like this:

[ C# ]

// First, import System.Web.Security namespace where Membership class is located
using System.Web.Security;
 
// Then, on button's click or on some other event, call DeleteUser method
protected void DeleteUser_Click(object sender, EventArgs e)
{
 // Finally, delete user Mike
 Membership.DeleteUser("Mike");
}

[ VB.NET ]

' First, import System.Web.Security namespace where Membership class is located
Imports System.Web.Security
' Then, on button's click or on some other event, call DeleteUser method
Protected Sub DeleteUser_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDeleteUser.Click
 
 ' Finally, delete user Mike
 Membership.DeleteUser("Mike")
End Sub

How to delete member from SQL Server database

If SQL Server membership provider is used, users data are stored in SQL Server database. But, you can't just delete selected row from asp_users table because SQL Server will return error message like: "DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_aspnet_Me_UserI_7B264821'. The conflict occured in database 'test', table 'aspnet_Membership', column 'UserID'.".

If you still want to delete a user directly from the database (instead of using Membership.DeleteUser method), you need to delete some data from other tables first. Do this in this order:

1. Find what is user id. It is stored in UserID column in aspnet_Users table.
2. Execute these SQL queries:

DELETE FROM aspnet_Profile WHERE UserID = 'Selected user ID here'
DELETE FROM aspnet_UsersInRoles WHERE UserID = 'Selected user ID here'
DELETE FROM aspnet_PersonalizationPerUser WHERE UserID = 'Selected user ID here'
DELETE FROM aspnet_Membership WHERE UserID = 'Selected user ID here'
DELETE FROM aspnet_Users WHERE UserID = 'Selected user ID here'

As you see, nothing complicated here, you just can't delete rows from aspnet_users first, but it is a little bit boring and not so efficient to execute five queries whenever you want to delete a user. Because of that, I placed this SQLs in stored procedure. Procedure has only one parameter named @UserToDelete, which represents UserID of user we want to delete. You can create Delete_ASPNET_Member procedure with SQL like this:

CREATE PROCEDURE Delete_ASPNET_Member
     @UserToDelete nvarchar(100)
AS
BEGIN
 
 DELETE FROM aspnet_Profile WHERE UserID = @UserToDelete
 DELETE FROM aspnet_UsersInRoles WHERE UserID = @UserToDelete
 DELETE FROM aspnet_PersonalizationPerUser WHERE UserID = @UserToDelete
 DELETE FROM aspnet_Membership WHERE UserID = @UserToDelete
 DELETE FROM aspnet_Users WHERE UserID = @UserToDelete
 
END
GO

Now, when procedure is created, you can delete any user with single SQL query, for example:

EXEC Delete_ASPNET_Member 'f9d559be-698c-4a2e-9874-830af50a2750'

Also, you can modify this procedure to delete user by user name, instead of not so user friendly UserID field. Implementation could look like this:

CREATE PROCEDURE Delete_ASPNET_Member_By_Name
 -- This procedure works similar as Membership.DeleteUser method
 -- It has only one parameter which is user name of selected user
 @UserNameToDelete nvarchar(100)
AS
BEGIN
 
DECLARE @UserToDelete nvarchar(100)
-- Find UserID for chosen UserName
SELECT @UserToDelete = (SELECT UserID FROM aspnet_Users WHERE UserName = @UserNameToDelete)
 
 -- Delete selected member
 DELETE FROM aspnet_Profile WHERE UserID = @UserToDelete
 DELETE FROM aspnet_UsersInRoles WHERE UserID = @UserToDelete
 DELETE FROM aspnet_PersonalizationPerUser WHERE UserID = @UserToDelete
 DELETE FROM aspnet_Membership WHERE UserID = @UserToDelete
 DELETE FROM aspnet_Users WHERE UserID = @UserToDelete
 
END

Now you can delete user by user name, similar like when Membership.DeleteUser method is used. For example, SQL to delete ASP.NET member with user name Michael would be:

EXEC Delete_ASPNET_Member_By_Name 'Michael'

aspnet_Users_DeleteUser stored procedure

Note there is built in procedure named aspnet_Users_DeleteUser but it needs four parameters to delete users, so it is more powerful, but also more complex to use especially if you don't need all options. Parameters of aspnet_Users_DeleteUser procedure are:

- @ApplicationName - by default its value is "/"
- @UserName - user name to delete
- @TablesToDeleteFrom - integer parameter, value is usually 1 to delete user only from ASP.NET membership or 15 to delete user from all user tables
- @NumTablesDeletedFrom - output parameter, returns number of affected tables

Conclusion

If you manipulate users with ASP.NET server side code, Membership.DeleteUser method is simple and easy solution. Membership API will take care about to delete user from different providers (e.g. Access, SQL Server, Active Directory). But, sometimes you want to delete user directly from database and in this case SQL queries above can be useful solution. If you expect to do it often, you can consider stored procedure that automates this task.

Note that you can't get back visitor data once you delete it so be careful. Instead of deleting, sometimes is enough to move user to role with lower or without any rights (e.g. you can create role BannedUsers). On this way, you can undo your action if you change your mind or if you deleted user accidentally.

Happy coding!

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |