Logging AD User Actions (with deleted users)

Question

We are about to migrate an intranet web application from using a proprietary forms-based security to Active Directory. The application logs a variety of user actions, and there is a significant amount of data associated with user accounts. Our plan was to migrate all of these UserId columns in various tables: from a foreign key linking the proprietary system, to an Active Directory GUID. Login names are identical between the two systems, so migrating is not an issue.

However, we identified one major problem: Our security policy dictates that inactive users must be deleted from Active Directory. An orphaned GUID in our security logs makes the entries pretty meaningless to anyone viewing them.

How can an application maintain the human-readable basics (name, login, etc.) about a GUID that has been deleted from Active Directory?

We have considered the following options. One of these options may end up being the optimal, but we wish to try for better:

• Denormalize the log tables and store name/login instead of a GUID (okay for logs, not so much for active data.)
• Maintain a "cache" of AD object information where entries are never deleted
• Keeping the AD account but deactivating/locking it down

Answer 1

I wouldn't fully denormalize the log table, but instead store the pertinent AD information alongside the GUID, as Tim said. However, if you will need this AD information in other areas, cache it in your user table. I would recommend against changing your security policy.