locked
DATABASE Audit for SELECT statement [only] on a table RRS feed

  • Question

  • Hi,

    I have a question on database auditing on SELECT statements on a table.

    syntax:

    CREATE DATABASE AUDIT SPECIFICATION AuditSelectSpec
    FOR SERVER AUDIT Audit_Select_Host
    ADD (SELECT ON OBJECT::[STVDB].[HOST] BY [dbo])

    When I query the fn(), I also see INSERT/UPDATE/DELETE statements, but I wanted to audit only SELECTs, and my syntax is clearly showing I'm auditing only SELECT. I'm not sure what mistake I'm doing or what's the mistake I'm doing.

    Could you please help me, I need some hand?

    Version of SQL server: sql server 2012

    Another question related to auditing.

    How could I get SELECT statement start and end times(to calculate execution time of the query) using database auditing in sqlserver?

    Regards

    Raju 
    angani@gmail.com
    Wednesday, October 2, 2013 5:19 AM

Answers

  • The AdventureWorks sample at CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL) works even when you specify only SELECT as audit action.

    The only thing I can see going wrong is: Do you use the correct audit file name? In my case the audit can be read by using

    SELECT  *
    FROM    sys.fn_get_audit_file('C:\Program Files\Microsoft\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Payrole_Security_Audit_4256C1F0-3817-444D-9E9F-1372E60A877D_0_130251772943580000.sqlaudit',
                                  DEFAULT, DEFAULT);


    You'll get the audit file name with

    SELECT  name ,
            audit_file_path
    FROM    sys.dm_server_audit_status;



    • Edited by Stefan Hoffmann Wednesday, October 2, 2013 8:57 AM
    • Proposed as answer by Sofiya Li Friday, October 4, 2013 1:46 AM
    • Marked as answer by Sofiya Li Wednesday, October 9, 2013 9:47 AM
    Wednesday, October 2, 2013 8:55 AM
  • Hi Raju ,

    Try like this , you might have enabled audit for Insert/Update/Delete also , Kindly check it out .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Sofiya Li Friday, October 4, 2013 1:46 AM
    • Marked as answer by Sofiya Li Wednesday, October 9, 2013 9:47 AM
    Wednesday, October 2, 2013 1:02 PM

All replies

  • The AdventureWorks sample at CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL) works even when you specify only SELECT as audit action.

    The only thing I can see going wrong is: Do you use the correct audit file name? In my case the audit can be read by using

    SELECT  *
    FROM    sys.fn_get_audit_file('C:\Program Files\Microsoft\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Payrole_Security_Audit_4256C1F0-3817-444D-9E9F-1372E60A877D_0_130251772943580000.sqlaudit',
                                  DEFAULT, DEFAULT);


    You'll get the audit file name with

    SELECT  name ,
            audit_file_path
    FROM    sys.dm_server_audit_status;



    • Edited by Stefan Hoffmann Wednesday, October 2, 2013 8:57 AM
    • Proposed as answer by Sofiya Li Friday, October 4, 2013 1:46 AM
    • Marked as answer by Sofiya Li Wednesday, October 9, 2013 9:47 AM
    Wednesday, October 2, 2013 8:55 AM
  • Hi Raju ,

    Try like this , you might have enabled audit for Insert/Update/Delete also , Kindly check it out .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Sofiya Li Friday, October 4, 2013 1:46 AM
    • Marked as answer by Sofiya Li Wednesday, October 9, 2013 9:47 AM
    Wednesday, October 2, 2013 1:02 PM
  • You can consider using SQL Server profiler and SQL Server traces (besides manual auditing), but it could get very complex and difficult.

    There are some third party tools like SQL Compliance manager from Idera, and SQL auditing and compliance tool from ApexSQL. These are able to audit exact SQL statements that have been executed, including SELECTs.
    Monday, October 14, 2013 8:21 AM