October 31, 2011 at 9:24 pm
Comments posted to this topic are about the item Field Level Auditing using Change Data Capture - Part 1
November 1, 2011 at 12:59 am
How can you derive who made the field change after the fact?
November 1, 2011 at 1:34 am
November 1, 2011 at 2:18 am
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.
November 1, 2011 at 3:01 am
Very nice feature, thanks for explaining it.
But what if someone change data through SQL Management studio, how can i track that?.
Thanks
November 1, 2011 at 5:53 am
Dammit, I got all excited there until I realised it was an Enterprise feature.
November 1, 2011 at 7:06 am
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?
November 1, 2011 at 7:46 am
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
November 1, 2011 at 11:25 am
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.
November 1, 2011 at 11:46 am
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
November 1, 2011 at 1:53 pm
unless there is a way to see who made the change this is useless. we have analysts who use SSMS or MS Access to make changes to data outside of applications. sometimes we even have apps make changes that we need to track down. we use audit tables and triggers
November 1, 2011 at 6:17 pm
Nice article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2011 at 6:28 pm
yes, DDL changes are not captured for existing CDC change tables. The DDL statements are saved in the cdc.ddl_history table for what that is worth.
When a DDL statement is made to a table being tracked by CDC, to capture the changes, you have to copy the existing change table data to a temp table, make the same DDL changes to that temp table, stop and restart CDC on the table to start fresh with the changes, copy the temp table data to the new, updated change table, and then reset the minimum LSN in the cdc.change_tables table. This is a script I recently used to updated a table that was using CDC:
-- copy existing change data to a temp table
SELECT * INTO cdc.dbo_contract_item_CT_Temp FROM cdc.dbo_contract_item_CT
-- add changes to temp table
ALTER TABLE cdc.dbo_contract_item_CT_Temp
ADD [energy_benefit_savings] [int] NULL
-- disable CDC on contract_item table
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'contract_item',
@capture_instance = N'dbo_contract_item'
GO
-- re-enable CDC on contract_item, this will include the new column
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'contract_item',
@role_name = NULL, -- gating role if needed
@filegroup_name = N'eContractsCDC', -- use same filegroup
@supports_net_changes = 1
GO
-- insert temp table data into new change table
INSERT INTO cdc.dbo_contract_item_CT
SELECT * FROM cdc.dbo_contract_item_CT_Temp
-- update start LSN to match temp table
UPDATE cdc.change_tables
SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_contract_item_CT_Temp)
WHERE capture_instance = 'dbo_contract_item'
-- drop temp table
drop table cdc.dbo_contract_item_CT_Temp
November 3, 2011 at 11:02 am
Please don't confuse and misuse this feature. CDC is a good tool for data analysis purpose, but not auditing.
If you are working on DW and trying to analyze user bbehave CDC help. if you are looking for auditing, CDC is not your choice.
No Signature
November 15, 2011 at 9:07 am
This audit only deals with someone directly made database connection and made changes, if that was a designated single app login, that has to be recorded by the app. We have one app that does so by recording an auditlog table. Reminder to archive and cleanup that because it can get big fast.
Jason
Jason
http://dbace.us
😛
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply