SQL Server 2008 Change Data Capture, who made the

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?

Answer 1

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.

Answer 2

If you haven't come across Joe Barreto's Article on CDC, this might help.

http://blogs.technet.com/josebda/archive/2009/03/24/sql-server-2008-change-tracking-ct-and-change-data-capture-cdc.aspx

Answer 3

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

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.

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.

Answer 6

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.