SQL SERVER 2008 - Audit Script

Question

Post reply

SQL SERVER 2008 - Audit Script

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 11, 2011 at 4:20 am

#237479

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

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

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

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 1

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

Yousaf Khan

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 2

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

Yousaf Khan

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

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

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 4

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 5

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