Field Level Auditing using Change Data Capture - Part 1

  • Comments posted to this topic are about the item Field Level Auditing using Change Data Capture - Part 1

  • How can you derive who made the field change after the fact?

  • I will try this...feels tempting

    Tarek Omar

    Technical Manager / DBA


  • 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.

  • Very nice feature, thanks for explaining it.

    But what if someone change data through SQL Management studio, how can i track that?.


  • Dammit, I got all excited there until I realised it was an Enterprise feature.

  • 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?

  • 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

  • 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.

  • 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

  • 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

  • Nice article.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • 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'


    -- 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


    -- 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

  • 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

  • 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.


Viewing 15 posts - 1 through 15 (of 16 total)

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