Sunday, January 13, 2008

How to: Remove users from the ASP.Net membership database

I'm sure I've blogged about this in the past -or perhaps it's just in my "to blog about list" but I thought I would share this little ditty on the Sunday night.

If you ever need to delete a user from your ASP.Net Membership database this is a really useful SQL script to do just that (I often find that the ASP.Net web administration tool throws a SQL Exception while trying to delete a user).

To use the code to delete a user from the ASP.Net membership database simple identify the Guid of the user and enter it where I've written 'THE GUID OF THE USER HERE' and hit go :)

USE ASPNet
GO

DECLARE @UserId uniqueidentifier
SET @UserId = 'THE GUID OF THE USER HERE'

DELETE FROM aspnet_Profile WHERE UserID = @UserId
DELETE FROM aspnet_UsersInRoles WHERE UserID = @UserId
DELETE FROM aspnet_PersonalizationPerUser WHERE UserID = @UserId
DELETE FROM dbo.aspnet_Membership WHERE UserID = @UserId
DELETE FROM aspnet_users WHERE UserID = @UserId

The message I was referring to above usually looks something like the following:

Msg 547, Level 16, State 0, Line 9
The DELETE statement conflicted with the REFERENCE constraint "FK__aspnet_Us__UserI__17036CC0". The conflict occurred in database "ASPNetMemberships", table "dbo.aspnet_UsersInRoles", column 'UserId'.
The statement has been terminated.

I've not looked into why it's happening (I expect it's something to do with an incorrect install on my behalf) but I'm sure there's a solution for it. I know there are a couple of built in SQL scripts i.e. aspnet_Users_DeleteUser but they required more params to get working ;)

How to: Remove users from the ASP.Net membership database

Useful Links:            Comments [4]  Trackback

Tags: ASP.Net | Web Development

Tuesday, November 18, 2008 4:17:52 PM (GMT Standard Time, UTC+00:00)

Actually, I think the error should be a question of order:

BEGIN TRAN

DELETE FROM aspnet_Membership WHERE UserID = @UserID
DELETE FROM aspnet_Profile WHERE UserID = @UserID
DELETE FROM aspnet_Users WHERE UserID = @UserID
DELETE FROM aspnet_UsersInRoles WHERE UserID = @UserID

ROLLBACK TRAN

--COMMIT TRAN

Unfortunately, the app I'm working on doesn't have PersonalizationPerUser, but I suppose that order can also be figured out with the above script. Of course, you'll want to comment out 'ROLLBACK TRAN' and uncomment 'COMMIT TRAN', once there are no errors.

Gustavo

Wednesday, May 20, 2009 6:21:01 PM (GMT Daylight Time, UTC+01:00)

What about the Membership.DeleteUser method? Clean and simple...

John

Thursday, May 21, 2009 9:05:13 AM (GMT Daylight Time, UTC+01:00)

Useful thanks John however that doesn't help unless you boot up Visual Studio and write an application to delete the user...

Sometimes I just need to delete a user from the db direct ;)

Tim

Thursday, January 21, 2010 3:28:38 AM (GMT Standard Time, UTC+00:00)

Tim,
Thanks for this simple solution.

Being only an occasional SQL user, was driving me mad how to delete 3 orphan uses.

PG

Peter Griffith