Change Data Capture Question

Question

Change Data Capture Question

TravisDBA

SSCoach

Points: 15780
More actions
February 1, 2010 at 8:56 am

#386218

I can track DML changes to a specific table (INSERT,UPDATE, DELETE) and track the actual historical data and the time it was committed on the database using CDC. Which is all well and good. But I would also like to see the Login/User that Inserted/Deleted/Modifed the row as well. Is there a way to get this from any existing CDC tables, functions, or stored procedures?
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

You can only really enforce this if all your data access is through stored procs, or you place triggers on the table.

user_name() and similar functions will capture user info. But of questionable use if it's a web app where all users use a generic connection.

I don't believe there is an all singing and dancing solution to this - I'm pretty sure you'll need to code it yourself.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

I did actually think that CDC could store who made the change - sorry not implemented yet so having to think back to the demos of it I sat through.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

TravisDBA SSCoach Points: 15780 More actions · Answer 3

Thanks Grumpy,

I did start thinking yesterday why would Mickeysoft go to all the trouble of tracking historically changed data on SQL Server including what rows got inserted, deleted, changed, and when, but NOT include who made the change? :crazy: At least with Change Tracking you can use WITH CHANGE_TRACKING_CONTEXT() to associate changes with an application or client who made the change. Travis

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

Drikus Roux Say Hey Kid Points: 708 More actions · Answer 4

It would also seem slightly superfluous to use the WITH CHANGE_TRACKING_CONTEXT(context) because you would have to supply the context parameter (from the application). If you were anticipating a quick transition from a current trigger based auditing regime, this option will cost you code changes.

It is enigmatic that MS did not provide the ability to CDC to include user and application information as part of the change data capture - odd:hehe:

Lowell SSC Guru Points: 323518 More actions · Answer 5

would that mean you have to add a dml trace to the server so you can capture the user/application name/hostname and other information, i guess to complement the changes CDC can capture?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TravisDBA SSCoach Points: 15780 More actions · Answer 6

My point is it would have been a lot simpler if Mickeysoft would have just included the user column in the Change table while they were constructing CDC. It just makes no sense. DBA's and managers need to know not only what data changed and when, but who changed it as well...:crazy:

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

Drikus Roux Say Hey Kid Points: 708 More actions · Answer 7

Yah! TravisDBA, I can't agree with you more, it's just plain silly!

Lowell - You don't need to use a trace - your application should be aware of who is using it to perform DML operations. You pass the information into the sproc (from the app), which (in the case of CDC) updates your meta data (the data on which you need auditing) with (let's say) the user and application details (in the actual table), which is picked up by CDC as columns changed and thus recorded. With Change Tracking, using WITH CHANGE_TRACKING_CONTEXT(context) you should use the context as the parameter passed from you application, to track the "who dunnit" or whatever you pass. This is my cursory understanding of the situation presently. I'm not sure how good change data is (for auditing) if you're unable to link the "who" to it? I certainly can't toss my current trigger based auditing without a stern impact analysis and possible changes to applications.

Hope this helped you?:-D