SQL Server Audit Policy

SQL Server allows you to get quite granular with audit policy. That is important if you are to avoid auditing needless activity which could affect performance and wastes storage.

SQL Server groups auditable actions into convenient categories called Audit Action Groups. You can enable action groups for auditing on a specific database or across the entire server. Action groups cover all auditable actions except for the following Transact-SQL commands which are enabled individually on specific database objects (e.g. tables, stored procedures) for specified users or roles:

  • Select
  • Insert
  • Update
  • Delete
  • Execute
  • Receive
  • References

SQL Server provides 3 kinds of objects for configuring which actions groups are audited and where the events are logged.

  1. At the server level (actually "instance" level) you create an Audit object. In the Audit object you specify audit log output options.
  2. Also at the server (or "instance") level you create a Server Audit Specification. This object defines which actions will be audited at the server level. Within the Server Audit Specification you enable one or more Action Groups. Action Groups are categories of related actions that can be audited.
  3. Finally, within databases you can create Database Audit Specifications where you define which Action Groups will be audited within that particular database.

On a given instance of SQL Server, you can create multiple Audit objects, Server Audit Specifications and Database Audit Specifications. You must relate each audit specification to a specified Audit object; the events generated by that server or database audit specification will be logged according to the settings in the associated Audit object. Since you can create multiple audit objects you can actually create multiple audit logs each containing events generated by whatever action groups are enabled within Audit's associated audit specifications.



Additional Resources