Database Level Auditing with SQL Server 2014

--// First create server audit 
CREATE SERVER AUDIT [my_server_audit]
TO FILE
(
FILEPATH = N'E:\My Work\DBA'
,MAXSIZE = 10 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(
QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

ALTER SERVER AUDIT [my_server_audit] WITH (STATE = ON);
GO

--// NOW CREATE DATABASE AUDIT SPECIFICATION

USE Test_Data
GO
CREATE DATABASE AUDIT SPECIFICATION [my_dbaudit]
FOR SERVER AUDIT [my_server_audit]
ADD ( SCHEMA_OBJECT_ACCESS_GROUP )
,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )
,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )
,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )
WITH ( STATE = ON);

--// To check the audit file result after executing some queries on the query analyser 

CREATE TABLE employee
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    salary INT NOT NULL,
    department VARCHAR(50) NOT NULL
 )
INSERT INTO employee 
VALUES (1, 'Mark', 'Male', 9000, 'Sales')

SELECT * FROM employee

SELECT * FROM sys.fn_get_audit_file
(
'E:\My Work\DBA\*.sqlaudit', DEFAULT, DEFAULT
)
GO

----// To Disable/Enbable the server audit and database audit specification.

USE MASTER
GO
ALTER SERVER AUDIT [my_server_audit] WITH (STATE = OFF);
Go

USE Test_Data
GO
ALTER DATABASE AUDIT SPECIFICATION [my_dbaudit] WITH (STATE = OFF);

Go