SQL SERVER 2008 - Audit Script

  • Hi,

    I executed my test server for the collecting audit details. after that I ran one insert statement on table (test), data was inserted but how can see what data inserted on table? I review audit logs but there is no details.

    Please tell me, where should i see the information?

    CREATE SERVER AUDIT [Audit-2011-11042011]

    TO FILE

    ( FILEPATH = N'C:\DBScript\Audit'

    ,MAXSIZE = 2048 MB

    ,MAX_ROLLOVER_FILES = 2147483647

    ,RESERVE_DISK_SPACE = OFF

    )

    WITH

    ( QUEUE_DELAY = 1000

    ,ON_FAILURE = SHUTDOWN

    )

    GO

    CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]

    FOR SERVER AUDIT [Audit-2011-11042011]

    ADD (BACKUP_RESTORE_GROUP),

    ADD (FAILED_LOGIN_GROUP)

    WITH (STATE = OFF)

    use [TK432]

    CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]

    FOR SERVER AUDIT [Audit-2011-11042011]

    ADD (DELETE ON DATABASE::[TK432] BY [dbo]),

    ADD (INSERT ON DATABASE::[TK432] BY [dbo]),

    ADD (update on DATABASE::[TK432] BY [dbo])

    WITH (STATE = OFF)

    GO

    create table test (number int)

    insert into test values (1)

    insert into test values (2)

    insert into test values (3)

    Thanks

    ananda

  • hi ananda,

    i think there is no other way to find the data which is insterd except that need to create some manual tricks to this.

    here two posible ways.

    1). you should add a field in your table with name created_date

    and insert the value to the table with getdate() to created field.

    2). the second way is insert the vlaue in a log table that will be a user defind table

    and you should execute trigger on you table (after insert/update) to enter the vlaue with(timestamp and user)

    to the log table.The trigger will take the value from objetcs deleted or inserted and it not posible to view

    directly the data from these objects.

    with these both methods you will able to view any data by order that you insert in the table.

    yousaf Khan

  • Trigger code will be like this

    create trigger [tablenameinserted] on [tablename] for insert

    as

    set rowcount 0

    set transaction isolation level read committed

    select field_names from tablename

    select field_names,user,timestamp / getdate() from inserted

  • Hi yousaf Khan ,

    Thanks for your reply..

    Can you suggestion me, Is there maintainence required if once deploy at production database level for purging audit details weekly or monthly basis.?

    thanks

    ananda

  • Yes Ananda,

    I will suggest the maintenance for audit detail if it is on production database because if the users

    running update and and insert statements then it will take more space because when the audit detail is deployed it creates a table and a trigger. it will save the whole rows of the base table to audit table and this process take more space to implement.

    please see bellow link.

    http://www.dosql.com/blog/?p=27

    Yousaf Khan

  • Hi yousaf Khan ,

    I am not using Trigger based auditing level for each tables.

    Just configured 1. Server Level Audit, 2. Database Level Audit only

    Server level

    --------------

    FAILED_LOGIN_GROUP

    DATABASE_OBJECT_CHANGE_GROUP

    SCHEMA_OBJECT_CHANGE_GROUP

    Database Level

    --------------

    DELETE, INSERT, UPDATE, SELECT, EXECUTE.

    Both are working fine & confirmed throu View Audit Logs. so please tell me how to manage audit logs on monthly basis? or Is there way to purging audit logs?

    Thanks

    ananda

Viewing 6 posts - 1 through 5 (of 5 total)

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