Recommendation for Security Auditing for databases – with example for Microsoft SQL Server

In this article, I want to share what I consider an essential minimum security audit definition for database systems, using Microsoft SQL Server as example.

As my readers know, Auditing is one of the most important security controls that we have. It is thanks to Auditing that we can prove who did what, and also what has not been done. This can be done by analyzing the “Audit trail”, a concept which I describe here: Security concept: Audit Trail.

In short, auditing – which should always include both logging and (automated) review – helps you to:

  • To prevent breaches if you detect attacks in the reconnaissance-scanning phase
  • Limit breaches by detecting breaches early

And of course, in the containment and recovery phase:

  • To determine which systems need to be contained
  • To track back how a breach occurred
  • To be able to determine if it has been successfully contained

Otherwise, how could you be sure that you found all breached accounts and potential backdoors if you have no logs of such activities going back to the time when the attacker gained access to the system?

Real-world examples of breaches facilitated due to a lack of Auditing

This lack of an active audit has bitten many companies, including Microsoft, when it was breached by russia’s foreign intelligence service, using the group Midnight Blizzard in 2024, and only many months later, the breach and even later the vastness of it emerged. In an attempt to contain the unknown, a huge effort that included closing unused Azure subscriptions and months of Engineering efforts was undertaken, the effects of which still ripple on. Many months of potential new feature development were lost which shows in the small set of security additions in SQL Server 2025. (So, whenever you hear me criticize this fact, it does not mean I am blaming the PMs or engineers, as I know the reasons behind this. All I hope is that leadership listens to customers again and invests in the security team rather than keeps slimming it.)

Lack of Auditing was a major handicap in the SolarWinds / Orion Supply‑Chain Breach 2019/2020: “The government couldn’t tell how they got in and how far across the network they had gone. It was also “really difficult to tell what they had taken.” https://www.wired.com/story/the-untold-story-of-solarwinds-the-boldest-supply-chain-hack-ever

And 2019, the Capital One breach went undetected for months until the attacker boasted about his success online.

The list goes on.

That is why I am a staunch proponent that every SQL Server and other database system should have a security audit active by default, just like Windows has it for decades.
Currently, Auditing must be turned on and configured after setup.

Auditing in Azure SQL Database – renewed infrastructure

The standard Audit that gets activated in Azure SQL database via the Azure portal is essentially a catch-all audit, which captures every single event (BATCH_COMPLETED) – an overkill for many systems.
I do not have the numbers for the overhead that the new architecture, which was rolled out mid-2025 (Enhanced Server Audit for Azure SQL Database: Greater Performance, Availability and Reliability ), but as a general rule, no observation comes for free, even if the overhead is much lower now.
Of course, most importantly, the new auditing infrastructure does not have the issue of lost events, which the former Auditing architecture for Azure SQL DB had (SQL Server Auditing did not have this issue). With the new architecture, Azure SQL DB Auditing is now enterprise ready.

The goal: an Audit that captures any directly security-impacting activity

My bare-minimum(!) recommendation for any customer is:

Every operation that alters the security configuration of the system should be audited – even if there is no specific regulatory requirement.

In some environments, those would also need to be under Separation of Duties. If you are familiar with SoD, you might be aware that Auditing is a classic example of a compensating control.
Another strong argument can be found in the NIST-standards for Security and Privacy Controls for Information Systems and Organizations: The Audit and Accountability (AU) control family specifies under Control Number AC-6 for LEAST PRIVILEGE: “Log the execution of privileged functions”.
With this background, here are some of the events to always Audit:

These are some of those events to always Audit:

  • Creating new accounts (Logins or Users)
  • Changing passwords of Logins/Users
  • Changing permissions
  • Adding accounts to highly privileged roles/groups
  • Impersonating another account (basically assuming the other account’s permissions)
  • Turning off Encryption, i.e. TDE (Transparent Data Encryption)
  • Altering ownership of objects (in systems using Discretionary Access Control (DAC) such as SQL Server, this directly influences permissions)

All these activities can leave the system in a less secure state than before and that’s why these activities should be audited = logged and reviewed. No matter which database engine or offering.

Auditing means: Logging and Reviewing. Logging alone does not help prevent anything.

Plus: always audit for successful account breaches and attempts thereof

In addition to the above list of activities, I recommend to also always audit both successful and failed logon events. While these do not fall under the definition of impacting the security configuration, these are always critical events to monitor for the following reasons:

  1. To detect if someone tries to breach an account (Logon Failure)
  2. Or, if a breach was successful (Logon successful)
  3. And, thirdly, to develop a pattern for what Logon times and User-combinations are normal. This will also help determine if a successful Logon is suspicious.

While this article uses the example of Microsoft SQL Server, all these recommendations are the same for any other database system, whether it’s Azure SQL database in Fabric, PostgreSQL, Oracle,  DB2 or Snowflake. It’s just the technique and capabilities that vary. At the end of this article, I will provide links to get started with Auditing for some of these systems.

Should you audit more than that?

You may think, “Why not audit for any security violations by default?”.

My answer: You absolutely should. If you have a definition of what comprises a security violation in your environment, please absolutely do audit those activities as well.

My recommendation is what should be done for every system at the bare minimum.

That is why I draw the line for activities that change the security posture rather than try to look for Data Exfiltration, for example. I cannot decide this for any system type generically. That is why I do not include BACKUP events in this audit. Yes, it could be initiated maliciously and be considered Data Exfiltration. However, so could be any SELECT-statement. But instead of blindly recommending everyone to audit every type of access on every system I decided to draw the line at security-posture influencing events as a general recommendation.

If you know you need specific other events, do not feel discouraged. More is better, as long as you know what you can use it for.

Note: Avoid Alert Fatigue
Overloading your team with an excessive volume of captured events and running SIEM systems without proper configuration is counterproductive. Without tuning your SIEM to fit your specific environment, you risk drowning in noise, missing real threats, and exhausting your resources on false positives.
– For some of our customers in financial services for example, my company (Sarpedon Quality Lab LLC) has audits and alerting in place that watch out for certain type of data access that can be signs of a data breach. But we carefully tune our algorithms to not end up with Alert Fatigue.

Now let’s get to the specific events for SQL Server Auditing.

List of Audit actions to capture

Here is the list of events as they are available in Microsoft SQL Server for the audit specification that include the above listed activities:

— Before creating this Audit Specification, you need to create an Audit which I am not showing here. I am using the name SQL_Default_Audit but of course you use whatever you defined.

CREATE SERVER AUDIT SPECIFICATION [SQL_Default_AuditSpecification]

FOR SERVER AUDIT SQL_Default_Audit – name of the Audit created beforehand

— server scope

  ADD (AUDIT_CHANGE_GROUP)

, ADD (DBCC_GROUP)

, ADD (EXTGOV_OPERATION_GROUP)

, ADD (SERVER_OBJECT_CHANGE_GROUP)

, ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)

, ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP)

, ADD (SERVER_OPERATION_GROUP)

, ADD (SERVER_PERMISSION_CHANGE_GROUP)

, ADD (SERVER_PRINCIPAL_CHANGE_GROUP)

, ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP)

, ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)

, ADD (SERVER_STATE_CHANGE_GROUP)

, ADD (LOGIN_CHANGE_PASSWORD_GROUP)

–Connection attempts

, ADD (FAILED_LOGIN_GROUP)

, ADD (SUCCESSFUL_LOGIN_GROUP)

, ADD (FAILED_DATABASE_AUTHENTICATION_GROUP)

— database scope

, ADD (DATABASE_CHANGE_GROUP)

, ADD (DATABASE_OWNERSHIP_CHANGE_GROUP)

, ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP)

, ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)

, ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)

, ADD (DATABASE_PRINCIPAL_CHANGE_GROUP)

, ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP)

, ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)

WITH (STATE = ON)

GO

Limiting background noise in SQL Server Auditing

If you use this Audit specification, you will find out that your Audit Log quickly pollutes with hundreds, if not thousands, of SELECTs coming from either Action_ID VSST = VIEW SERVER STATE or VW = VIEW (COLUMN ENCRYPTION KEY or COLUMN MASTER KEY) per day.
Those are read-access to system views and mostly come from background tasks. For example, SQL Server Management Studio runs them for every refresh. In my opinion, those should have their own Audit_Group and not be mixed under actual Server or database “operations” since they don’t do anything. They are called by SSMS in the background, which is why you get these massive amounts.

As long as those events do not receive their own Audit Action Groups we need to use the Filter property in the Audit itself to filter those harmless activities but massive amounts of data before storing them. (In SQL Server, the Audit object defines the target and is created before the specification, which I show above.)
To filter out selects on server scoped dynamic management views, you would need to add the following in the Audit filter:

([ACTION_ID]<>1414746966) /*VSST = VIEW SERVER STATE*/

I hope that saves you some time and headache.

Important note on SQL Server 2022

If you are on SQL Server 2022, make sure to have at least Cumulative Update 19 installed. Because SQL Server 2022 had a bug in Auditing for SQL Server 2022, which prevented the SERVER_PERMISSION_CHANGE-Audit Action from capturing events, which is part of this list for good reasons. This was fixed with CU 19.

Hopefully, this helps with rolling out security auditing on your SQL Server installations and thus increases the security of your SQL Server systems.

Note on STIG-compliance

If you are operating within the Department of Defense (DoD) network, are a defense contractor, or an organization interacting with the DoD supply chain, you need to comply with Security Technical Implementation Guides (STIGs), published by Defense Information Systems Agency (DISA)

Coincidentally, one of the contributors for the SQL Server STIGs, Adrian Rupp, kindly reviewed this article 🙂

This Audit recommendation of this article will give you a good start and help you to comply with many of the Audit-related rules. But you will need to audit additional activities to comply with all Audit-related STIG rules. For example, you also need to audit access to schema-objects and other things. You can use the described Audit as a start and add a complementary Audit in addition or extend this Audit.

This is great, but you require security assurance and want to be notified of suspicious events?

If you’re looking for an expert to review your environment and specific requirements—helping not only with auditing but also with setting up processes to parse data and automatically alert on suspicious activity – my team and I would be happy to assist. (Inquiries here). – That includes efficiently assessing your systems based on the latest STIGs for Microsoft SQL Server.
If you want to learn more about what we can do, talk to us.

Lastly: help improve Auditing in SQL Server

If you want to help improving Auditing in SQL Server, here are a few Feedback-items which you can upvote to help the Auditing team prioritize those issues:

  1. Implicit addition of database user is not audited
  2. Document ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS wait type
  3. RESTORE VERIFYONLY should not be captured under CREATE DATABASE Audit Action group

Links to get you started with Auditing for your database system

Here are the promised links that may serve as a starting point for auditing different database systems:

Happy Auditing

Andreas

Reviewed by: Adrian Rupp, Senior Consultant at Sarpedon Quality Lab LLC and former Principal Program Manager for SQL Auditing at Microsoft and contributor to security compliance requirements for the U.S. Department of Defense.

1 reply
  1. tejaswini
    tejaswini says:

    Great read—security auditing is often underestimated but absolutely critical. Implementing robust access controls, monitoring schema and data changes, and periodically validating your audit processes are key to maintaining a resilient SQL Server environment.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *