Menu
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?
591
At the basic database level you can track changes by having a separate table that gets an entry added to it via triggers on INSERT/UPDATE/DELETE statements. Thats the general way of tracking changes to a database table. The other thing you want is to know which user made the change.
Change data capture (CDC) uses the SQL Server agent to record insert, update, and delete activity that applies to a table. This makes the details of the changes available in an easily consumed relational format.
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.
199
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
