Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when.

It can be configured for individual tables (and in the case of updates individual fields even)

Great detailed article here - https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

IMPORTANT:

You need SQL server Agent running

Or else changes aren’t tracked

There is an automatic clean up job

By  default the length of retention is set for 4320 minutes = 72 hours = 3 days. It can be configured by:

sp_cdc_change_job @job_type='cleanup', @retention=minutes

The maximum value is 52494800 (100 years). If specified, the value must be a positive integer. Retention is valid only for clean up jobs.

You can extract the time of the change – but not who made it

Unless of course you table has ModifiedBy and ModifiedOn type fields – in which case providing these are updated by the app you of course do also have an archive of who changed what when

CDC is only enabled on those fields in the table that exist when you enable it

If you enable CDC then add some more fields to your table these will not be tracked.  You need to drop and reapply CDC to that table (which will remove your audit history …)

Commands overview

Enabling CDC on a database:

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Sites',
@role_name     = NULL

Enabling CDC on a table:

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Sites',
@role_name     = NULL

Removing CDC from a table:

EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name   = N'Sites',
@capture_instance = 'all'

List changes to a table:

SELECT *
FROM cdc.dbo_Sites_CT

Get time mapping

(If you need to get time data direct from CDC)

SELECT *
FROM cdc.lsn_time_mapping