Hi, We already implement a similar mechanism inside our product: for each table, dedicated fields are in charge to store the InserUserName and LastUpdatedUser. We can extend them, adding the DeletedUserName column.
Anyway it is not acceptable to implement a logical delete for us, while it is possible to think to a solution that updates the DeletedUserName before physically deleting the record.
Alternatively, an elegant solution could be: SQL Server stores a dedicated variable inside the CDC table, i.e. CDCUserName. By default, CDCUserName contains the connection user name (SQL auth or Win auth), so the console changes are logged too.
At runtime, the application should be able to assign CDCUserName with the current application user for that specific session only. The mechanism should be compatible with connection pooling.
Yes, it is an exiciting feature. My concern is about the security trace: is it possibile to trace the current user application name ? Normally we connect application to SQL using a unique trusted connection (windows authentication), but we are interested to log all db changes along with the current logged (application) user; otherwise we know the db changes but not who made them.