Configure Database Audit Specifications
1. In this example a Database Audit Specificaton will be created on the AdventureWorks Database. A DBA can Audit every Select, Insert, Update and Delete Operations on Production.Product Table.
2. A DBA can create a Database Audit on the AdventureWorks database by expanding Databases | AdventureWorks | Security and right clicking Database Audit Specifications and selecting New Database Audit Specification… from the menu:
3. SQL Server 2008 will open up a new window Create Database Audit Specification, as shown in the image below:
4. Within the grid in the Audit Action Type choose SELECT from the drop down list, in the Object Class select OBJECT. Next you need to click on the button beside Object Name text box, this will open up a dialog box to select objects, in this under Enter the object names to select type [Production].[Product]. Next you need to click the button next to Principal Name and select PUBLIC by browsing the objects in the dialog box and finally click OK:
5. Repeat the operation for Audit Action Type INSERT, UPDATE and DELETE. The final screen will look like as shown in the below snippet. Click OK to Save the Database Audit Specification:
6. Once the Database Audit Specification is created it needs to enabled. This can be done as shown in the snippet: