Cannot delete user with Web Site Administration Tool

[Guest]

When using the Web Site Administration Tool, I get the following error when
trying to delete a user in a web application I configured to use membership,
roles, and profiles:

"An error was encountered. Please return to the previous page and try again.

The following message may help in diagnosing the problem: The DELETE
statement conflicted with the REFERENCE constraint
"FK__aspnet_Me__UserI__24285DB4". The conflict occurred in database
"GalleryServerPro2", table "dbo.aspnet_Membership", column 'UserId'. The
statement has been terminated. at
System.Web.Administration.WebAdminPage.CallWebAdminHelperMethod(Boolean
isMembership, String methodName, Object[] parameters, Type[] paramTypes) at
ASP.security_users_manageusers_aspx.Yes_Click(Object sender, EventArgs e) at
System.Web.UI.WebControls.Button.OnClick(EventArgs e) at
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) at
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,
Boolean includeStagesAfterAsyncPoint)"

The data store is SQL Server 2005, and this is all on my development PC.
Ultimately, the problem can be traced to the following line in the stored
procedure aspnet_Users_DeleteUser:

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

For some reason, the "SELECT from sysobjects..." is not finding a match,
causing the condition to fail, which means the following line does not
execute:.

DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId

Because this does not execute, the following subsequent SQL fails with the
constraint violation I listed at the beginning:

DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId

The "SELECT name FROM sysobjects..." should return a match. In fact, it does
when I execute the query manually in a query window ("SELECT name FROM
sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V')"
returns 1 record).

It boils down to this: The stored procedure is behaving as stated when using
the Web Site Administration Tool or calling Membership.DeleteUser() from an
ASPX page. However, when I step through the proc manually, using right-click
"Step Into Stored Procedure" in VS 2005, and giving it the same initial
parameter values, it works correctly! That is, the "SELECT name FROM
sysobjects..." query does not find a match using WSAT or DeleteUser(), but it
does find a match when executing the proc manually.

How can it be that the proc behaves differently depending on who calls it?
As best I can tell, the parameters are exactly the same. More importantly,
what do I need to do so that the Web Site Tool and the DeleteUser() function
works?

Another user reported the issue but without resolving it:
http://groups.google.com/group/micr...MembershipUsers&rnum=5&hl=en#51d8f7776077ffe0

Thanks for any help!
Roger Martin
www.galleryserverpro.com
www.techinfosystems.com

 

[Steven Cheng[MSFT]]

Hi Roger,

From your description, you're using the ASP.NET membership service and when
using the VS 2005 Web Administration site to delete the users in the
membership database, you got error indicate that the "constraint" is
violated, correct?

Based on my research, I haven't found any exiting documented issue of the
membership delete behavior or of the web administration site. I think the
problem is likely caused by something incorrect in the ASP.NET membership's
container database. Is this the first time you encounter this error or is
it working correctly originally?

Currently, if there hasn't contained many exiting important membership data
in that database, I suggest you use aspnet_reqsql.exe to reconfigure
it(remove and readd those services). Or you can connecct to another
confgured database for testing to see whether this problem also occur.

If you have any other finding, please feel free to post here also.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thank you. Uninstalling and then reinstalling using aspnet_regsql fixed the
issue.

I have a suspicion that Team Edition for Database Professionals has
something to do with this. I have previously noticed that if you use Team Db
to import a schema from a database with the membership/role/profile
configuration, then immediately do a schema compare back to the source,
several of the membership/profile/role objects are considered "different".
When I encountered this, I clicked "Write updates" to synchronize the two,
even though they should have been identical. I didn't study the reported
differences too closely, but it had something to do with collation or some
other seemingly minor issue.

In retrospect, those differences may have played a role here. I will watch
for this in the future, and I offer this message as a helpful caution to
others.

Regards,
Roger Martin
www.galleryserverpro.com
www.techinfosystems.com

 

[Steven Cheng[MSFT]]

Hi Roger,

Thanks for your reply.

Yes, I've also met some similar problems from other members when try
manually moving the ASPNETdb. In most cases, it will occur runtime error
(when using the membership or other provider) due to the new database
misses some of the required components(store procedure or constraint....).

Anyway, I'm glad that you've got it working now. If you meet any new
problem or anything we can help in the future, please feel free to post
here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.