I should have noted that users are using connection pooling on shared connections. Primarily web applications. So, SQL Server user and process functions will not properly identify a user.

The column "created_by" and the proposed column "deleted_by" are pointers to an internal "user_id" in a separate table. Client applications are expected to properly record the user's id. Additionally, these columns are monitored for possible misuse and each user can report activity they did not initiate.

Logging UPDATE and DELETE transactions to a record would further allow us to monitor the types of changes to any given record for each user.

It would be easy to create an interface to the database through stored procedures that requires a "user_id" for each transaction. However, this would eliminate bulk operations on multiple records.

My solution thus far is to add the "deleted_by" column, *trust* that client applications will populate the column correctly, deny attempts to delete any records with where "deleted_by IS NULL" through an "INSTEAD OF DELETE" trigger and log all other deletes, and use an "INSTEAD OF UPDATE" trigger to log all updates, but ignore updates limited to the "deleted_by" column alone.

The only other option I can think of is to introduce the logging into the data access layer code. However, I would like to attempt to keep this logic in the database, since I feel the concept deals with data security and integrity rather than data gathering and communication.