SQL Server 2008 in-built Auditing Functionality has a lot of advantages over traditional way of auditing:
1. SQL Server 2008 Auditing is Easy to Setup and Administer.
2. SQL Server 2008 Auditing can be enable at the Instance and Database Levels.
3. SQL Server 2008 Auditing includes many predefined activities that you can audit, including DML and DDL activity.
4. SQL Server 2008 Auditing is very lightweight compared to older ways of capturing this information which used Traces. It internally uses Extended Events engine to capture audit data. This results in fast performance plus minimum overhead compared to using SQL Trace to capture activity.
5. SQL Server 2008 Auditing has a high Granularity in auditing like SELECT, INSERT, UPDATE, DELETE, REFERENCES and EXECUTE statements can be captured for individual users at the object level.
6. SQL Server 2008 Auditing can Capture and also view Audit Results. The Audit results can be captured and stored in disk files, or in the Server’s Event Logs. Data stored in Event Logs can be viewed using the Event Log Viewer.
Quick Step by Step approach to setting up Auditing is given below:
1. First begin setting up SQL Server Audit in Security under “Audit” Functionality
2. SQL Server Audit offers two different types of Audit Specifications which are:
•Server Audit Specifications - used to audit an activity that occurs at the SQL Server instance level, such as auditing login and logout activity.
•Database Audit Specifications - used to audit an activity within a database, such as who is inserting, selecting data from a particular table in database.
After Specifications defined under Database and Server go to next step.
3. Start/Enable the Audits.
4. Reviewing Audit Events.
5. To Edit Audits, the Audits needs to be disabled first. After Editing it, re-enable them.
Quick screenshots of this Auditing setup as shown below:
