locked
Auditing Reading of an individual table RRS feed

  • Question

  •  

    Hi,
    What I need to do is to audit not changes but access.

    Ideally would like to do it for just one table (that has more sensitive data), of course I could log all logins and attempts. I figured my way would be better for
    - performance, less writes
    - easier for me to keep an eye on, as we will only be recording a small proportion of transactions.


    Anyway look forward to any views/ideas, thanks
    Dave
    Monday, December 1, 2008 6:13 PM

All replies

  • In SQL Server 2008 (Enterprise and Developer Editions) it is possible using the new auditing infrastructure. For example, assuming we have a table named [dbo].[x] and a server Audit called [Audit-20081114-173214]:

     

    CREATE DATABASE AUDIT SPECIFICATION [Table_x_audit_spec]

    FOR SERVER AUDIT [Audit-20081114-173214]

    ADD (SELECT ON OBJECT::[dbo].[x] BY [public])

    WITH (STATE = OFF)

    GO

     

    -- Optionaly, you could add more actions to be audited on this audit spec

     

    ALTER DATABASE AUDIT SPECIFICATION [Table_x_audit_spec]

    WITH (STATE = ON)

    GO

     

    For more detailed information please visit BOL:

    * Database Audit Specification (General) http://msdn.microsoft.com/en-us/library/cc280472.aspx

    * CREATE DATABASE AUDIT SPECIFICATION (T-SQL) http://msdn.microsoft.com/en-us/library/cc280404.aspx

     

    If you have additional questions or feedback please let us know.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Monday, December 1, 2008 6:36 PM
  • If you need to support SQL Server versions prior to SQL Server 2008 you may try Hedgehog tool from Sentrigo: http://www.sentrigo.com/

    It is very sophisticated software and allows you flexible auditing with minimal impact on performance.

     

    Dmitriy

    Tuesday, December 2, 2008 8:18 AM
  •  

    Thanks a lot Raul, unfortunately I forgot to mention this DB is still on 2k! I didn't know about that feature on 2008 so thanks for the note.

     

    Dave

    Tuesday, December 2, 2008 8:46 AM
  • Dmitriy,

    Thanks for your help, will give the Standard version a try.

     

    Dave

    Tuesday, December 2, 2008 8:51 AM