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

12. Now, you are required to obtain the audit action groups on the database level using
the sys.dm_audit_actions system catalog, which returns abridged results
related to database level actions. Let us query the information by using the following
TSQL:
SELECT name
FROM sys.dm_audit_actions
WHERE configuration_level = 'Action' AND
class_desc = 'OBJECT'
ORDER BY name
13. The query mentioned earlier returns the atomic events on the object securable scope
such as DELETE , EXECUTE , INSERT , RECEIVE , REFERENCES , SELECT , and UPDATE .
For mission-critical financial applications the audit of events
is important and of all the atomic events mentioned, SELECT
is highly advantageous as it offers a granular level of audit
without any performance hindrance.
14. Use the following statement to create the database audit specification:
CREATE DATABASE AUDIT SPECIFICATION AdventureWorks2008R2_DB_
AuditSpec
FOR SERVER AUDIT DBIASSQA_Marketing_Server_Audit
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (INSERT, UPDATE, DELETE
ON Sales.Customer
BY public)
WITH (STATE = ON)
GO
15. Now, the setup of the database level audit specification is complete, it will capture
both the audit action groups and audit events. The final step is to enable the server
audit using the following TSQL:
ALTER SERVER AUDIT DBIASSQA_Marketing_Server_Audit WITH (STATE=ON)
This completes the steps to implement auditing and service-level security features on an
existing SQL Server instance.