Auditing

If implementing an auditing strategy is paramount to your business to satisfy regulations such as the Health Insurance Portability and Accountability Act (HIPAA), the Sarbanes-Oxley Act (SOX), and the Payment Card Industry Data Security Standard (PCI-DSS), then leveraging SQL Server 2019 to achieve this is possible with SQL Server Audit. With this feature, you will be able to ensure accountability for actions made against your SQL servers and databases, and you can store this log information in local files or the event log for future analysis, all of which are common goals of an auditing strategy.

To implement SQL Server auditing, first the main audit should be created at the server level, which dictates where the files will be located for information to be logged to. From this main audit, you can then create a server-level audit specification. At this level, you will be able to audit actions such as server role changes and whether a database has been created or deleted. Alternatively, you can scope this feature to the database level via a database-level audit specification, where you can audit actions directly on the database schema and schema objects, such as tables, views, stored procedures, and functions (see https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15 for a full list of the capabilities for both server- and database-level auditing).

The following example shows the code required to audit a specific table, [HumanResources].[EmployeePayHistory], for a DELETE activity using a database AUDIT specification:

USE [master]
GO
CREATE SERVER AUDIT [MainAudit]
TO FILE 
(  FILEPATH = N'D:\AUDIT\'
  ,MAXSIZE = 1024 MB
  ,MAX_FILES = 10
  ,RESERVE_DISK_SPACE = OFF
)
WITH
(  QUEUE_DELAY = 1000
  ,ON_FAILURE = CONTINUE
  ,AUDIT_GUID = 'A164444-d7c8-4258-a842-9f2111f2c755'
)
ALTER SERVER AUDIT [MainAudit] WITH (STATE = ON)
GO
USE [AdventureDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [DeleteAuditHR]
FOR SERVER AUDIT [MainAudit]
ADD (DELETE ON OBJECT::[HumanResources].[EmployeePayHistory] BY [dbo])
GO
DECLARE @files VARCHAR(200) = 'D:\AUDIT\*.sqlaudit';
SELECT * FROM sys.fn_get_audit_file (@files, default, default)

Figure 2.32: The results statement dialog

As you can see, it is very simple to set up auditing, and you can do so with minimal performance overhead.