Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 267

8. For this recipe, we will track the server-scoped actions such as backup, restore, failed

logins, database object access, and server state groups by using the following TSQL
to audit the audit groups mentioned earlier:
CREATE SERVER AUDIT SPECIFICATION DBIASSQA_Marketing_Server_Audit_
Spec
FOR SERVER AUDIT DBIASSQA_Marketing_Server_Audit
ADD (BACKUP_RESTORE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_OBJECT_ACCESS_GROUP)
ADD (SERVER_STATE_CHANGE_GROUP)
WITH (STATE = ON)

9. Again, let us run the TSQL to obtain the configuration and validation of the newly
created server audit specifications using the system catalog sys.server_audit_
specifications as follows:
SELECT sas.server_specification_id,
sas.name, sas.is_state_enabled,sasd.audit_action_name from
sys.server_audit_specifications as sas inner join sys.server_
audit_specification_details as sasd
on sas.server_specification_id=sasd.server_specification_id

The results are as follows:

server_specification_id name is_state_enabled

audit_action_name
65536 DBIASSQA_Marketing_Server_Audit_Spec 1

DATABASE_

OBJECT_ACCESS_GROUP
65536 DBIASSQA_Marketing_Server_Audit_Spec 1 BACKUP_

RESTORE_GROUP
65536 DBIASSQA_Marketing_Server_Audit_Spec 1 FAILED_LOGIN_

GROUP
65536 DBIASSQA_Marketing_Server_Audit_Spec 1 SERVER_STATE_

CHANGE_GROUP

10. The result set returns the individual audit action names that are enabled for the
DBIASSQA_Market_Server_Audit_Spec specification.
11. To track the server scope and database level usage, before we enable the server
audit object, add the database level audit specification object.