As a SQL Server DBA, maintaining visibility into who accessed your data, what they did, and when they did it can be crucial for security and compliance. Auditing and monitoring are your database’s security cameras, logging and alerting you to potential risks and suspicious activity. Audit is a feature I’ve not used much in my career, but we have had some interesting setups amongst our client base, so it’s worth a reminder of what we can do with this tool.

SQL Server’s Audit feature is a powerful tool for tracking activity. By creating a server audit object, you define where logs will be stored—such as the Windows Security log or a local file. Then, you set up audit specifications to record specific events, like SELECT, INSERT, or UPDATE actions on sensitive tables. For example, if your organization needs to monitor access to financial records, you can create an audit that tracks who reads or modifies that data. This provides a reliable trail for compliance or forensic investigations.

Real-time monitoring can be equally critical if you need to go beyond a retrospective look at what happened and try and get in front of a treat. While auditing captures a historical record, monitoring tools like Extended Events or SQL Server Profiler help you identify and respond to issues as they happen. For instance, monitoring can alert you to unusual login patterns or excessive data exports, giving you the chance to intervene before serious damage occurs.

The tools we’ve been given for audit purposes are actually really powerful, but as with all things they don’t come for free. Efficient auditing and monitoring require a balance. Logging every action can overwhelm you with data, so focus on high-risk activities—like failed logins, changes to critical tables, or permission alterations. Lower-risk logs, such as simple SELECT queries, can be archived for periodic review if they are required at all. We have one client where audit of server access is a critical requirement and the windows event logs these are written to is many multiples the size of the database itself.

Clients will frequently start with ‘everything’ as a requirement, but when you drill into what ‘everything’ actually is, and how much it will cost, this needs to be scaled back. It’s usually not just about catching bad actors—it’s about demonstrating that your are taking appropriate steps to protect your data and detect unauthorised attempts to access or manipulate it.