locked
select ,insert,update and delete is not getting recorded in the database audit spec. RRS feed

  • Question

  • Hi,

    I am using SQLSERVER 2008 Standard Edition and i m trying to implement database audit for few database. Following is the script that i used:

    CREATE

    SERVER AUDIT [audit_mirror]

    TO

    SECURITY_LOG

    WITH

    (

    QUEUE_DELAY = 1000

     

    ,ON_FAILURE = CONTINUE

     

    )

    GO


    CREATE DATABASE AUDIT SPECIFICATION test_aud1 FOR SERVER AUDIT Audit_mirror
      ADD (DATABASE_OBJECT_CHANGE_GROUP),    ADD(INSERT, UPDATE, DELETE,SELECT     ON Schema:: dbo    
      BY
    [BUILTIN\Administrators], [db_accessadmin], [db_backupoperator], [db_datareader], [db_datawriter], [db_ddladmin], [db_denydatareader], [db_denydatawriter], [db_owner], [db_securityadmin], [dbo], [public]);

    Both the above audit spec were created successfully. But it is not auditing the select,insert,update and delete issued on the schema.

    Am i missing anything. Do u have any solution for this.

    Thanks

    Venkat

     

     

     

     

     

    Wednesday, March 30, 2011 7:26 AM

Answers

  • I think you miss the STATE=ON

    CREATE SERVER AUDIT [audit_mirror]
    TO
    SECURITY_LOG
    WITH
    (
    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    )
    GO

    CREATE DATABASE AUDIT SPECIFICATION test_aud1 FOR SERVER AUDIT Audit_mirror
      ADD (DATABASE_OBJECT_CHANGE_GROUP)
      ,ADD(INSERT, UPDATE, DELETE,SELECT ON Schema::dbo   
      BY
    public
    )
    WITH (STATE = ON)

    here is good sample http://www.sqldbatips.com/showarticle.asp?ID=136

    • Proposed as answer by Stephanie Lv Sunday, April 3, 2011 7:33 AM
    • Marked as answer by Alex Feng (SQL) Wednesday, April 6, 2011 11:52 AM
    Thursday, March 31, 2011 8:53 PM

All replies