SQL Server Change Data Capture - Capture user who made the change

Question

Concerning SQL Server Change Data Capture, can you track the User who has made the change to the row/column data or is there anyway to extend CDC to allow this? I couldn't see anything in the documentation.

Answer 1

You can't capture username with CDC..

You have to use Auditing to do so or if this is a one time request,you can query TLOG..

Below is the connect item requesting the same..

CDC : options to capture more data (username, date/time, etc)

You also can use triggers as per this article Playing with CDC in Katmai from Aaron Bertrand..

Create table :

CREATE TABLE cdc.dbo_test_CT_MoreInfo
(
startlsn BINARY(10),
seqval BINARY(10),
operation INT,
username SYSNAME NOT NULL DEFAULT SUSER_SNAME(),
eventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (startlsn, seqval, operation)
);
GO

create trigger:

CREATE TRIGGER cdc.LogMoreCDCInfo
ON cdc.dbo_test_CT
FOR INSERT
AS
BEGIN
  IF @@ROWCOUNT > 0
  BEGIN
    INSERT cdc.dbo_test_CT_MoreInfo(startlsn,seqval,operation)
      SELECT __$start_lsn, __$seqval, __$operation FROM inserted;
  END
END
GO

Answer 2

You can create a new field where the user’s details, machine, time etc. are stored and updated after each change.

Also, there are third party tools you can use for users auditing – SQL Audit & Compliance manager. I’ve used both and you can’t go wrong with any of them. There are probably more tools like those out there.