How to audit table access with SQL Server Audit

Knowing who accesses sensitive data is one of the foundations of data governance. SQL Server Audit, a native SQL Server feature, is the most reliable way to audit table access: it automatically records every read or change and helps answer the question "who saw this information, and when?". The whole setup is done in T-SQL and takes just a few minutes.

Prerequisites

• A SQL Server instance (on-premises, on a VM, or Azure SQL Managed Instance).
• SQL Server Management Studio (SSMS) or Azure Data Studio to run the commands.
• Administrator permissions — specifically ALTER ANY SERVER AUDIT at the server level.
• A folder on the server to store the audit files, for example C:\Auditoria\.

Before you start, it helps to know the three parts of an audit: the Server Audit (where events are written), the Audit Specification (what gets recorded), and the target (a file or the Windows log). This separation makes the system flexible — a single Server Audit can receive events from several databases. We will configure them in that order.

Step 1: Create the audit object

The first step is to create the Server Audit, the "vault" where events will be written. In this example we write to a file inside the folder we prepared; SQL Server creates .sqlaudit files and rolls them over as they fill up.

USE master;
GO
CREATE SERVER AUDIT Auditoria_Acessos
TO FILE (FILEPATH = 'C:\Auditoria\', MAXSIZE = 100 MB)
WITH (ON_FAILURE = CONTINUE);
GO

The ON_FAILURE = CONTINUE option lets SQL Server keep running even if, for some reason, it cannot write a record — it prevents the database from stopping. In highly regulated environments you might prefer SHUTDOWN, but CONTINUE is the safest choice to begin with.

Step 2: Enable the audit

When it is created, the audit object is turned off. We need to enable it explicitly:

ALTER SERVER AUDIT Auditoria_Acessos
WITH (STATE = ON);
GO

From now on the "vault" is ready to receive events. All that is left is to tell it what to record, which is what we do in the next step.

Step 3: Define what to audit

Now we create a Database Audit Specification that describes the actions to record. In this example we want to know who runs a SELECT against the dbo.Salarios table in the RH database — a typical case of sensitive data. The BY public clause covers every user.

USE RH;
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_Select_Salarios
FOR SERVER AUDIT Auditoria_Acessos
ADD (SELECT ON dbo.Salarios BY public)
WITH (STATE = ON);
GO

You can add more lines inside ADD (...), separated by commas, to also audit INSERT, UPDATE or DELETE on other tables. Recording only what matters keeps the files small and easy to analyse.

Tip: avoid auditing very busy tables (such as application log tables) with SELECT. The volume of records grows quickly and makes analysis harder. Start with tables that hold personal or financial data.

Step 4: Read the records

The events are stored in the .sqlaudit files. To read them as a readable table, use the system function sys.fn_get_audit_file, pointing to the files with a wildcard:

SELECT event_time,
       server_principal_name,
       database_name,
       object_name,
       statement
FROM sys.fn_get_audit_file('C:\Auditoria\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Each row shows the user, the date and time, and the command that was run. You can filter the result by user or by period by adding a WHERE clause, which is handy once the files already contain many events — exactly the trail you need to investigate an improper access or to prove GDPR compliance.

Check the result

To confirm everything works, run a test query against the audited table and read the log again:

SELECT * FROM RH.dbo.Salarios;   -- the action we want to catch

When you run the sys.fn_get_audit_file query from the previous step again, a new row should appear with your user name and the SELECT command. If that row shows up, the audit is working and recording access in real time.

Conclusion

In four steps you have built a mechanism that records who accesses sensitive data — an essential pillar of any governance policy and a frequent requirement in compliance audits. The next step is to forward these records to a central location, such as the Windows security log, and set alerts for access outside normal hours. Which table in your organisation should be the first to enter this audit?