Server Level Audit using TSQL

Hi Friends,

In my previous blog I describe about auditing and auditing setup using management studio. You can go to that blog by using this link;

Today here we will setup the server level audit using TSQL:

Step 1: Create Server Audit

Use master
go
CREATE SERVER AUDIT AUDITDEMO
TO File (FilePath='E:\AuditLogs\'
    , MAXSIZE = 1024 MB    
    , MAX_ROLLOVER_FILES = 5  
    , RESERVE_DISK_SPACE = ON)
WITH (QUEUE_DELAY=1000, ON_FAILURE=CONTINUE)

Note: Here disk space will be reserve (after enabling the AUDITDEMO).

Step 2: Create Server Audit Specification

Use master
Go
CREATE SERVER AUDIT SPECIFICATION SERVERAUDITSPECDEMO
FOR SERVER AUDIT AUDITDEMO
ADD (FAILED_LOGIN_GROUP)

Step 3: Enable both

Use master
Go
ALTER SERVER AUDIT AUDITDEMO
WITH (STATE=ON)
GO
ALTER SERVER AUDIT SPECIFICATION SERVERAUDITSPECDEMO
WITH (STATE=ON)

Step 4: You can also check that both options are on or not by using below DMVs. If both query return value 1 that means both are enabled

Use master
GO
Select is_state_enabled from sys.server_file_audits
Select is_state_enabled from sys.server_audit_specifications

Step 5: JUST DO A TRIAL OF FAILED LOGIN using SSMS (Here I just try to login with a login name DSSS, which not exist on server)

Step 6: Now you can read your Audit log file by using

select event_time,server_principal_name,statement from fn_get_audit_file('E:\AuditLogs\*',null, null)

Output of above code is as shown below:

HAPPY LEARNING!

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook