ASP.Net Membership.DeleteUser

Question

In testing, the user on a db i've used was a big jefe. in production, he only has Execute.

When i called

Membership.DeleteUser(user)

in testing, it worked. I try the same in production, and i get this:

The DELETE statement conflicted with the REFERENCE constraint "FK__aspnet_Us__UserI__37703C52". The conflict occurred in database "Testing", table "dbo.aspnet_UsersInRoles", column 'UserId'.

In my seargles (searches on google), I came across this link: http://www.dougdossett.com/Details.aspx?Detail=35 where the dude was saying, "

Error: The DELETE statement conflicted with the REFERENCE constraint "FK__aspnet_Me__UserI__15502E78". The conflict occurred in database "YourDBName", table "dbo.aspnet_Membership", column 'UserId'.

Took me a while to find a solution to this across multiple sites and options as the error and possible solutions were rather misleading. Turns out, at least in my case, it was a problem with permissions on the membership database. The user I'm using to connect had access to view the membership details within the database itself, but as part of the aspnet_Users_DeleteUser stored procedure it selects from the sysobjects table. The membership connection user apparently did not have sufficient rights to do that select so the overall delete failed.

The fix for me was to add the user to the aspnet_Membership_FullAccess role for the membership database. "

but when i did that it didnt work. Anyone have any ideas on how to deal with this?

Answer 1

I believe your 'REFERENCE' constraint is actually a Foreign key in the database that exists between the aspnet_Users table and the aspnet_UsersInRoles table. I would figure that the user you are trying, has it's UserId in both tables, and before you can remove it from the Users table, it has to be removed from the UsersInRoles table also.

Have you tried http://msdn.microsoft.com/en-us/library/system.web.security.roleprovider.removeusersfromroles.aspx to ensure that all the roles are removed from this user? You could verify too by inspecting the rows of these two tables in the database.

Answer 2

Hmm, KyleB, i dont think that's it. Mind you, it's letting me delete the user, when i use a SQL server user who is in dbo and has sa and all that. But when i try with a user who only has Execute permissions, it does buss. So, i wanted to know specifically what permissions i need to give to that user who only has Execute permission. Thanks

Answer 3

Gang! Alert! Alert! So i'm still searching the google for issues like this to resolve it, and i came across this link: http://www.findwebhosting.us/aspnet-membershipdeleteuser/

IT'S THE EXACT SAME POST I ORIGINALLY PUT UP, in some other kind of "chain up" forum. ...anybody ever notice that?

Answer 4

OK, guess what? I read this: http://forums.asp.net/t/1254087.aspx

Ok, few minutes after sending my post I found the solution :) It turns out that SELECT PERMISSION had to be added for ASPNET user on vw_aspnet_MembershipUsers view.

But it is still mystery why I didn’t get an error concerning lack of permission. EXIST statement was just returning false.

and gave the production user SELECT permission and voila! It works! Thanks guys!

Answer 5

If the error (or similar) still persists after granting the ASP user SELECT on the vw_aspnet_MembershipUsers, you may want to grant SELECT for some of the other vw_aspnet_???? views too. Especially "profile" and "UsersInRoles". Otherwise - for some reasons, the DeleteUser SP gets an empty result when SELECTING from those views and refuses to delete existing entries from them first.

Answer 6

I also had this issue and it was caused by missing views, to correct I just used the create script from another database and recreated all the vw_aspnet_* views.

Answer 7

After a little inspection I found the issue is this line in the aspnet_Users_DeleteUser stored procedure:

IF ((@TablesToDeleteFrom & 1) <> 0 AND
    (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))

There are 3 other similar lines for 3 other tables. The issue is that if the user executing the stored proc doesn't have access to vw_aspnet_MembershipUsers it won't turn up when selecting from sysobjects. I'm curious to know why that whole EXISTS statement is necessary.

Regardless, the following discussion, "Access to sysobjects to view user tables without having access to the user tables directly in SQL Server Security", has the answer. By granting "VIEW DEFINITION" on the views in question, the EXISTS statements will now succeed and you don't have to grant unneeded, unwanted, or excessive permissions to the user in your application's connection string.