SQL Server Audit records who did what. A server audit is the destination (a file or the Windows log). A server audit specification captures instance-level events such as failed logins and permission changes; a database audit specification captures actions in one database, such as reads or changes to a sensitive table.

Step 1: Create the audit destination

USE master;
CREATE SERVER AUDIT [DBA_Audit]
TO FILE (FILEPATH = N'E:\SQLAudit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 20)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

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

Step 2: Server-level events

CREATE SERVER AUDIT SPECIFICATION [DBA_Server_Audit_Spec]
FOR SERVER AUDIT [DBA_Audit]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

Step 3: Database-level actions

USE [YourDB];
CREATE DATABASE AUDIT SPECIFICATION [YourDB_Audit_Spec]
FOR SERVER AUDIT [DBA_Audit]
    ADD (SELECT, UPDATE, DELETE ON OBJECT::dbo.SensitiveTable BY public),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);

Step 4: Read the audit

SELECT event_time, action_id, succeeded, server_principal_name,
       database_name, object_name, statement
FROM sys.fn_get_audit_file(N'E:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

event_time is in UTC.

Good points

• Built in, and much lighter than a Profiler or server-side trace.
• AUDIT_CHANGE_GROUP records any change to the audit itself, so switching it off leaves a trace.
• Database audit specifications are available in every edition from SQL Server 2016 SP1.

Bad points

• Auditing reads on busy tables (SELECT … BY public) produces a very large volume of records.
• ON_FAILURE = SHUTDOWN stops the instance if the audit can’t be written. Use it only where policy requires it.
• The audit volume needs space and monitoring, and someone has to review the output, or it has no value.

From my SQL Server DBA handbook.