concept server audit specification in category sql

appears as: Server audit specifications, server audit specification
SQL Server MVP Deep Dives

This is an excerpt from Manning's book SQL Server MVP Deep Dives.

Server audit specifications define the audit action groups that you want to audit at the instance level, along with the server audit they belong to. There can be a maximum of one server audit specification per server audit. You can create multiple server audit specifications as long as each one uses a separate server audit.

Creating server audit specifications

For the first example (demonstrated in listing 4), we’ll create a server audit specification to audit changes to logins using the SERVER_PRINCIPAL_CHANGE_GROUP and use the ApplicationLog server audit we created earlier. If you don’t specify the WITH clause, the server audit specification will be created in a disabled state. We’ll also generate some events for the audit to capture.

Listing 4. Creating a server audit specification using the Application Log target
CREATE SERVER AUDIT SPECIFICATION [AuditLoginChanges] FOR SERVER AUDIT [ApplicationLog] ADD (SERVER_PRINCIPAL_CHANGE_GROUP) WITH (STATE = ON) GO -- create some events CREATE LOGIN AuditLoginDemo WITH PASSWORD = 'sdkfds*)&(9kdsafk', CHECK_POLICY=OFF GO ALTER LOGIN AuditLoginDemo WITH DEFAULT_DATABASE = model, DEFAULT_LANGUAGE = British GO DROP LOGIN AuditLoginDemo GO

In the next example, we’ll create a server audit specification using SSMS that audits the SERVER_OPERATION_GROUP and SERVER_STATE_CHANGE_GROUP audit action groups and uses the ServerAuditFile server audit. To create a new server audit specification, follow these steps:

  • Click OK to create the server audit specification.
  • Figure 7. Creating a server audit specification using SSMS