SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Field Level Auditing using Change Data Capture - Part 1


Field Level Auditing using Change Data Capture - Part 1

Author
Message
David Balthrop
David Balthrop
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 55
Comments posted to this topic are about the item Field Level Auditing using Change Data Capture - Part 1
kmslogic
kmslogic
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 15
How can you derive who made the field change after the fact?
tarek.omar
tarek.omar
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 129
I will try this...feels tempting


Tarek Omar
Technical Manager / DBA
myblog
Martin Nyborg
Martin Nyborg
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 35
In "normal" web application it is not the logged in user that access the database, but a single trusted nt user or a sql user in the connecting string.

So where is the username coming from?

Best Regards Martin Nyborg - Perfection is a process, not an end-point.
nadersam
nadersam
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2002 Visits: 522
Very nice feature, thanks for explaining it.
But what if someone change data through SQL Management studio, how can i track that?.

Thanks
rob.symonds
rob.symonds
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 3616
Dammit, I got all excited there until I realised it was an Enterprise feature.
-=JLK=-
-=JLK=-
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1515 Visits: 303
Nice article, thanks for the explanation and great examples. I'm quite tempted to change from my current auditng techniques to this as it would be much more efficient, however I do have one concern, how doe the CDC tables handle DDL changes? Our databases are constantly evolving (usually adding new fields, but some name changes). I use Powerdesigner to model the database and create change scripts, but it wouldn't affect the CDC tables, so would I have to drop the CDC affected tables and recreate them?
LSCIV
LSCIV
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 154
kmslogic (11/1/2011)
How can you derive who made the field change after the fact?


When my team tried it we couldn't get CDC to capture the hostname of the server/workstation/laptop that made the change. This bit of info is a must for our environment. It's rare for someone to make an unauthorized change under their own credentials, it's usually a case of someone hijacking the application credentials to make the change. Not that it happens often, but we've caught all of those individuals thanks to our current tool (apex sql audit) capturing the hostname. Just my .02
ShawnTherrien
ShawnTherrien
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 765
Martin Nyborg (11/1/2011)
In "normal" web application it is not the logged in user that access the database, but a single trusted nt user or a sql user in the connecting string.

So where is the username coming from?


The SYSTEM_USER function returns the context of the user who is running the transaction. This is what I've used in the past to track who has made a change in a Trigger.

Your application (ssms, web app, etc) is connecting using a SQL or domain account in it's connection string. This value is listed under the changed record.

So your web app might show ModifiedBy: WebApplicationLogin and you in SSMS might show ModifiedBy: domain\MNyborg.

With the application using a single account and using no impersonation, it's an extremely limited auditing feature, but in my experience it's nice to know if the application made a change or if a specific developer made a change. It would be amazing to get the specific user of the application who made the change, but my web development team doesn't want to deal with it.
ShawnTherrien
ShawnTherrien
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 765
This article looks very promising.

I've got my own trigger based CDC (field at a time) capture set up created by dynamic SQL pointed at a table.

I would prefer to have this working so we don't have that complexity, but I recall we got nervous about DDL changes and my triggers handled that case more gracefully I recall.

It's been about a year, but I think when you reconfigure a CDC on a table, it wipes out the audit log it's been keeping, so you have to be really careful about backing up that audit log when you add a new record. My triggers just won't record the new fields until I run my script on the table again, but the audit table always remains.

It's been a while, I'm going to run some tests with this
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search