locked
Database audit for 1 table RRS feed

  • Question

  • I would like to enable delete and update transactions audit on 1 specific table in the database on MS SQL 2012. Is this possible? If yes what are the steps to enable such auditing? Thanks.
    Tuesday, September 24, 2013 9:09 AM

Answers

  • You can use change tracking

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Sofiya Li Thursday, September 26, 2013 7:39 AM
    • Marked as answer by Sofiya Li Friday, October 4, 2013 8:59 AM
    Tuesday, September 24, 2013 9:29 AM

All replies

  • You can use change tracking

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Sofiya Li Thursday, September 26, 2013 7:39 AM
    • Marked as answer by Sofiya Li Friday, October 4, 2013 8:59 AM
    Tuesday, September 24, 2013 9:29 AM
  • You can try Change tracking 

    http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

    or  Change Data Capture 

    https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 24, 2013 9:33 AM
  • Our you can use DML trigger + an own auditing table.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 24, 2013 9:35 AM
  • If you just want to "audit" the event - as opposed to the changed data, why not stick to auditing? - It has much less overhead.

    For a description how to set up auditing look here: http://technet.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Tuesday, September 24, 2013 12:35 PM
  • Whenever you think "auditing", you should start by determining what information you need in your audit. There are several techniques, some easier some required more overhead and work. Several techniques are mentioned already in this thread, but we cannot give you form recommendations without knowing your requirements.

    Do you need to see who did the change? Change tracking and change data capture doesn't tell you this.

    Do you need to see the data change? Depending on how the app work, audit might not give you this info, only prm placeholders.

    Are you OK with writing and maintaining a trigger? 

    Etc...


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, September 24, 2013 5:31 PM
  • Yes, you can take advantage of change tracking like others already mentioned, or the change data capture option.
    Also, you can try auditing DML triggers. This type of audit triggers fires upon INSERT, UPDATE and DELETE statements, as well on stored procedures that execute DML like operations

    Here is a good article on this topic

    You can consider doing this by reading the transaction log too. For this, you can use undocumented DBCC LOG and fn_dblog functions, or try some third party transaction log reader tools.

    Thursday, October 3, 2013 11:35 AM