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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


29
0
Sign in
to vote
ID 283707 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 6/21/2007 10:21:37 AM
Access Restriction Public

Description

Change Data Capture is a valuable feature and a huge step forward in DBA auditing.

However, I find that the information provided is the bare minimum.  If I was log fluent or had one of the expensive log reading tools, I might be able to figure out who is responsible for that update, what server they ran it from, and exactly when it happened.
Sign in to post a comment.
Posted by gbw4444 on 12/1/2016 at 9:23 AM
Considering all the features added in 2016....and Microsoft's new attitude and release cadence...this should be revisited.
At least ADD the User who made the change! SUSER_SNAME()
Posted by SeanLange on 6/12/2015 at 1:07 PM
Sadly without this ability CDC is good enough for a checkbox list of features but falls woefully short of being actually anything resembling useful in the real world.
Posted by ncage on 4/13/2015 at 8:52 AM
Even though CDC was not designed for auditing why not enhance it so it "can" be used for auditing? Simply adding a timestamp of when the change occurred and what user caused the change would make it MUCH more useful and possibly eliminate some expensive 3rd party auditing tools. We are about to migrate to sql server 2014 and was hoping we could get rid of our current auditing software (Idera) but it looks like we won't be able to now.
Posted by Aging Hippie on 9/19/2013 at 10:45 AM
Without this requested feature, CDC serves no useful purpose other than to point out Oracle's superiority to SQL Server.
Posted by LostAtC5 on 5/17/2013 at 7:13 AM
Although CDC was not originally designed as an auditing feature, everyone I know is using it for exactly that reason. IMHO very little additional effort would be needed to add the additional logigng features, such as determining who made the change. Yes I can work around this but it's a big PITA.
Posted by Russ Thomas on 2/4/2013 at 3:34 PM
I recognize the DW focus of the CDC process but I have to agree that being able to easily identify the date/time of a change or the source of the change (i.e. user) would almost seem like a given in a change capturing scenario. Further, it seems like a trivial addition to what's already there... even if it were an optional set of fields that could be enabled in order to avoid the extra disc space consumption in a default situation.

I hate to drop the big O but oracle does it in their CDC implementation.

http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90237/cdc.htm#1042945
Posted by Microsoft on 8/28/2007 at 6:09 PM
CDC is not designed to be a auditing feature. The feature is targeted at data warehousing scenario and the information exposed through the change table is meant to provide enough information to optimize ETL processes. Currently i dont believe the log exposes the mentioned information

But engine will have an auditing feature in CTP6 which will allow you to do the kind of auditing you mentioned

Thanks
Gopal
Posted by Greg Y on 8/19/2007 at 9:34 PM
As you are probably aware, CDC was not designed for auditing which is why this was resolved as by design.