Ah. Audit trails.

CDC will work for that. There are a number of other options, each with its own pros and cons.

I generally prefer to use triggers that capture the changed columns in a simple XML format. Triggers can capture things like what user ID, that CDC can't. And getting XML to only record the columns that were actually changed is dead easy.

One piece of advice on audit trails and logging: You don't need to record the "new" value. The table already has that. All you need to record is the old value (for Update) or the whole row (for Delete), but you don't need to record Insert at all, nor new values on Updates. Because the table has those values. Saves a ton of storage space.

I even have a bit of code that can automate the trigger creation, and can even automate changing the triggers if the table schema is modified.

Would have mentioned all that earlier, but didn't realize you were looking for an audit log.