SQL Server – How to handle the removal of ASP.NET users when their user ID is linked to records that must be retained even after deleting a user?

I have a website that uses ASP.NET membership and so there are several "aspnet_" tables in our database.

We create a service for users that essentially allows them to create laboratory test orders for an employer. An employer can have multiple users who can order tests.

If a person actually loses access to the service (she is fired, resigns, retires or her permissions are revoked by her employer), the laboratory test orders she's created must remain in the system, as well as the user name of the user. This way, when someone is viewing a list of all the lab commands, he knows which user has ordered which test and for whom.

At the present time, when a user loses access, we physically delete his user data record from the aspnet_users and aspnet_membership tables. This removes their user name, email, profile, password, and so on. We can not do this if we create a relationship between the aspnet_users table and the table containing the lab commands. If we do, we must either:

  • cascading deletion (which will delete commands created by a user)
  • set the value in the table containing the lab commands to "NULL" or some kind of default value. If we do, we lose all the user information, such as his username, etc., that we wish to keep.

If we opt for a "software removal" approach and simply mark that a user is not allowed to log in (untrusted), but keeps his user record in the aspnet_users and aspnet_membership tables is there a problem that may arise from what I can not see? Security issues? Performance problems?

I usually think that leaving user data in a system when the user can not access the system is just … wrong. This leaves a bad taste in my mouth.

What is the best way to manage the storage of data of this type that should be kept even if the parent registration is deleted? Should not I use a relationship and simply copy the user data to another table? If I do, there is a chance for inconsistent data and updates would be difficult. I can easily see him becoming a monster and impossible to maintain. There is also a risk of conflict if a user name is reused in the future.

Any advice would be welcome.