Database Search Solution
(New Version) Search Control
Free ASP.NET Controls
ASP.NET Media Player Control
Flash Video Player Control
ASP.NET Telecommute Jobs
Free IP Location Lookup
Test .Net Regular Expressions
CSS/Table/DIV Page Layouts
Article Sites Master List
.NET Windows Forms
General .NET Framework
Accepting Credit Cards
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
[ VB.NET ]
' First, import System.Web.Security namespace where Membership class is located
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.
DELETE FROM aspnet_Profile 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
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
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 "/"
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.
comments powered by Disqus