MSSQL: Creating auditing in SQL Server.

This blog will demonstrate how to set up Audit in vCenter SQL Server database. 

There are 3 layers of auditing. They are Server-level, Database-level, and Audit-level. In this blog, I am going to cover the Database-level auditing on VCDB database..

An audit event is to use to track and auditing changes made to the database.

SQL Server Database Audit has 3 main parts.

• Server Audit. This to be created at the Master database level.
• Audit Specification. This to be created at the user database level.
• Turn ON both Server Audit and Audit Specification

The audit locations can be directed to either audit file, security event log or application event log. The following example will direct the audit information to an application log.

 Use Master  
 create server audit svr_audit to application_log  
 go  
 alter server audit svr_audit with (state=on)  

New Server Audit will show up under the database instance level.

 use VCDB_2016  
 create database audit specification db_audit_spec  
 for server audit svr_audit add (insert on dbo.vpx_version by public);  
 go  
 alter database audit specification db_audit_spec with (state=on);  

On the database tree, the new audit will show up and by default, it is OFF. This can be turned ON or OFF.

Query the status of the Server Audit status.

 select * from sys.dm_server_audit_status;  

Audit specifications can be located with the following query.

 select * from sys.database_audit_specification_details  

That's it. Auditing is useful if DBA needs to locate suspicious activities in certain objects.

Additional useful reference can be found at this link. 
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc280663(v=sql.105)