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?
If you haven't come across Joe Barreto's Article on CDC, this might help.
CDC really isn't designed for auditing. If you're looking for auditing capabilities, you should be using SQL Server Audit.
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.
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.
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.