SQL Server Audit feature

SQL Server Audit feature is one of the powerful and good feature provided by Microsoft to audit server-level and database-level groups of events and individual events. You can find more information on this in BOL or here at microsoft site http://msdn.microsoft.com/en-us/library/cc280663.aspx

In this post, I am going to explain how to deal with Inbuilt SQL Server Audit feature.

To setup and use this SQL Server Audit feature, First You need to follow below steps.

  1. CREATE A SERVER AUDIT WITH FILE SPECIFICATION FIRST TO HOLD AUDITS
  2. ENABLE THE NEWLY CREATED AUDIT AS BY DEFAULT IT IS DISABLED
  3. CREATE SERVER AUDIT SPECIFCATION SO AS TO WHAT ALL ACTIVITIES NEEDS TO BE AUDITED
  4. ENABLE THE NEWLY CREATED AUDIT SPECIFICATION AS BY DEFAULT IT IS DISABLED

Below is the T-SQL way of achieving this.

--CREATE A SERVER AUDIT WITH FILE SPECIFICATION FIRST TO HOLD AUDITS
USE [master]
GO
CREATE SERVER AUDIT [MSSDTestAudit]
TO FILE
(    FILEPATH = N'D:\MSSD_LAB\Audit'
    ,MAXSIZE = 5 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = ON
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
GO
--ENABLE THE NEWLY CREATED AUDIT AS BY DEFAULT IT IS DISABLED
ALTER SERVER AUDIT [MSSDTestAudit] WITH (STATE = ON)
GO
--CREATE SERVER AUDIT SPECIFCATION SO AS TO WHAT ALL ACTIVITIES NEEDS TO BE AUDITED
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [MSSDTestServerAuditSpecification]
FOR SERVER AUDIT [MSSDTestAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (BACKUP_RESTORE_GROUP)
GO
--ENABLE THE NEWLY CREATED AUDIT SPECIFICATION AS BY DEFAULT IT IS DISABLED
ALTER SERVER AUDIT SPECIFICATION [MSSDTestServerAuditSpecification]  WITH (STATE = ON)
GO
--READ THE AUDIT USING T-SQL, NOTE THIS IS A TRICKY WAY.
--YOU CAN READ THIS USING GUI BY RIGHT CLICKING ON AUDIT AND VIEW AUDIT LOGS
USE master
GO
--CREATE VARIABLE TO HOLD THE LOCATION OF AUDIT FILE
DECLARE @MyAuditFile NVARCHAR(MAX)
select @MyAuditFile = log_file_path+'*_'+CAST(AUDIT_GUID AS NVARCHAR(MAX))+'*' from sys.server_file_audits
--READ THE AUDIT FILE USING T-SQL
SELECT
EVENT_TIME,ACTION_ID,SUCCEEDED,SESSION_ID,CLASS_TYPE,
SESSION_SERVER_PRINCIPAL_NAME,SERVER_PRINCIPAL_NAME,
SERVER_INSTANCE_NAME,DATABASE_NAME,SCHEMA_NAME,OBJECT_NAME,
[STATEMENT] FROM
FN_GET_AUDIT_FILE(@MYAUDITFILE,NULL,NULL)
ORDER BY EVENT_TIME DESC,SEQUENCE_NUMBER

Another way of achieving this using SQL Server Management Studio GUI. You can find this here.