SQL Server 2008 Change Data Capture, who made the change?

Question

I asked a question on SOF a week or so ago about auditing SQL data changes. The usual stuff about using triggers came up, there was also the mention of CDC in SQL Server 2008.

I've been trying it out today and so far so good, the one thing I can't see it supports is keeping a track of who actually made the change. Who executed the statement?

I'm interested to know if anyone has used CDC for auditing and how you kept track of who made the change?

Slaver

Can I know how did u solve this problem? – Grapevine

Answer 1

I altered the CDC table directly using: ALTER TABLE cdc.dbo_MyTable_CT ADD UserName nvarchar(50) NULL DEFAULT(SUSER_SNAME())

BTW you don't need the date info since it's already in the start and end LSN fields.

My only problem is that my users login via a Windows Group which allows them modify rights, but the UserName field is always my username and not theirs. I haven't found a way around this problem.

Beniamino

The time is in the cdc.lsn_time_mapping table – Rotunda

this doesn't seem to work - I am getting "sa" in the column when I execute an UPDATE from a different login – Lw

Answer 2

Change Data Capture doesn’t track the user, machine who made the change, or time of change.

The best solution to track users who made the change with CDC is to create a new field to store users details, which will be updated on each change (found that idea here).

Another article in the same series lead me to a third party tool offering an out-of-the-box solution. I’m still in evaluating process, but it looks quite good so far. You can see the comparison of tracked information in a handy table at the end of this sequel.

Hope this helps.

Linolinocut

can you expand on " create a new field to store users details, which will be updated on each change". Is this like the answer above? Where will the user details come from? – Lw

Answer 3

CDC really isn't designed for auditing. If you're looking for auditing capabilities, you should be using SQL Server Audit.

Kegan

That's not strictly true. Whilst I understand that CDC is used for things like data warehousing, I have read a number of articles I've read suggest CDC can be used for auditing data changes as an asynchronous alternative to using triggers. Although agreed it wasn't designed for this. – Slaver

SQL Server Audit doesn't capture Before & After values. It boggles the mind that MS went through the trouble of implementing two approaches (CDC & SSA) and neither of them solves the problem of fully auditing changes. – Microgroove

@Microgroove I think there's actually three methods. CDC, Audit and Change Tracking. Currently designed a good before/after audit system using CDC and a mixture of simple triggers that have CONTEXT_INFO populated by the app with the user (because it's not using sql server users) and other state information. – Haughay

Answer 4

MrEdmundo, CDC is not ready for prime-time in my opinion. There currently seems to be quite a bit of struggle in regards to deploying a database project from Visual Studio with CDC enabled (it doesn't like DDL changes). Additionally, it seems that CDC has a built-in data end-of-life cleanup proc, so this may be bad times for you if you really mean to maintain your audit history for a long time.

Also, correct me if I've misunderstood, but it seems SQL Audit is geared for auditing a plethora of events that occur in SQL Server such as failed log-ins, DDL changes, etc.

Change Tracking is only for DDL and not DML, so you're out of luck there.

If your intention really is to capture the 'old' record that was either Updated or Deleted from a table, it seems the best answer is still to create an Audit.TableName and an update+delete trigger on dbo.TableName. Also make sure TableName includes columns of CreatedBy DEFAULT SUSER, CreatedDate DEFAULT getdate(), ModifiedBy, ModifiedDate.

Dewy

Answer 5

Although not ideal, the general consensus seems to be that CDC won't capture who made the change, but we've implemented CreatedBy/Date and UpdatedBy/Date columns which can be used to see who triggered the change. For that to work, of course, the SP or SQL statement updating the row needs to explicitly set the UpdatedBy/Date fields appropriately using suser_name() and getDate() respectively. I agree this would be nice to have out of the box and this is making CDC do something is wasn't meant for, but I too am trying to use CDC to audit data changes async instead of using traditional triggers.

Bicuspid

We are currently using CDC for audit tracking and this is the approach we went with. We added an audit user ID column to each table and modified our stored procedures to update that column. Might not be the prettiest solution, but it works. – Tole

Answer 6

Here is trigger which can be created through some automated process or manually when CDC is enabled on that specific table, this trigger will solve the problem that who & from where changes were made:

CREATE TRIGGER TR_TABLENAME_CDC 
ON TABLENAME
FOR INSERT, UPDATE, DELETE 
AS
DECLARE           
       @SessionID int,
       @AppName nvarchar(255),
       @HostName nvarchar(255),
       @UserName nvarchar(32)
BEGIN
    SELECT @SessionID=@@SPID
    SELECT @AppName=program_name, @HostName=host_name from sys.dm_exec_sessions where session_id = @SessionID

    IF(@AppName = 'BLAH BLAH' OR @AppName = 'XYZ' OR @AppName = 'ABC')
    BEGIN
        SELECT @UserName=login_name from sys.dm_exec_sessions where session_id = @SessionID
        INSERT INTO UserDetail (SessionID, AppName, HostName, UserName) VALUES (@SessionID, @AppName, @HostName, @UserName)
    END 
END

Mucous