Configure Server Audit Specifications in SQL Server 2008
1. Server Audit Specificatons can be created by right clicking the Server Audit Specificatons and selecting the option New Server Audit Specificatons…. From the menu:
2. SQL Server 2008 will open up a new window Create Server Audit Specification, you need to provide the details as shown in the image below:
3. Once the Server Audit Specification is created it needs to be enabled. This can be done as shown in the image below:
4. You will see a dialog box with the success message as shown in the image below once the Server Audit Specification is enabled successfully:
5. The next step will to execute the followingTSQL to backup the AdventureWorks Database:
BACKUP DATABASE AdventureWorks
TO DISK =N’D:BackupsAdventureWorks.BAK’
6. Once the backup is completed, right click the SQLServerAuditing under Securiity | Audits and select the option View Audit Logs from the menu.
7. SQL Server will bring up a dialog box that will show the details of the backup event.
Server Audit feature of SQL Server 2008 allows Database Administrators to enable SQL Server Instance level audits.