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

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.