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 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 didn't work. Anyone have any ideas on how to deal with this?

Answer 1

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 2

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 3

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 4

I solved this by removing the line in the proc which checks for the view. I don't have the any asp membership views and haven't needed them anywhere, so it seems pretty pointless to create the view just so that line of code can return true - the proc doesn't actually use the view. Perhaps if you use more features of the membership objects you might need the view for something else. Either way checking for the view's existence seems an odd way for the proc to decide whether the aspnet_membership table has a row it needs to delete.

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

Answer 5

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.

Answer 6

Maybe better to make sure the user that executes the delete membership has the to correct ASP.NET Membership sql roles. In my case I was deleting a membershipuser that has some roles and profile properties. The delete method failed, but after assigning the correct sql roles it worked.

ALTER ROLE [aspnet_Profile_FullAccess] ADD MEMBER [<YOUR SQL USER>]
ALTER ROLE [aspnet_Roles_FullAccess] ADD MEMBER [<YOUR SQL USER>]

You could also add [aspnet_Personalization_FullAccess] if you are using that functionality.