Auditing in SQL Server

Basically Auditing helps to monitoring any action on a database, using this feature you can audit any actions of server level as well as database level.

Purpose of Auditing
a) To ensure accountability of user actions
b) Prevent users to perform inappropriate actions
c) Monitoring any unusal actions
d) Log the unauthorized user actions.
e) Collect data about specific database activities.
f) Ensure complaince such as SOX (sarbnes-oxley act), HIPPA (Health Insurance Portability and Accountability Act.

Using this feature we can audit server actions, database actions such as DML and DDL operations, also we can log the output into a file, windows security event log or windows application event log.

Auditing is not new in SQL Server 2008, prior to this version auditing can be done using several ways like SQL Server Traces or Login Auditing and C2 Audit mode etc.,

When compare with prior versions SQL Server 2008 has various benefits.

a) Ability to audit events at database level or instance level
b) Ability to audit many different activities like DDL operations, DML operations.
c) Ability to capture and review audit results
d) Fast and Light weight – Because SQL Server 2008 uses extended events to caputre audit data. This is very minimal overhead when compared to SQL Server Traces.
e) Easy to setup

SQL Server Audit Components

SQL Server Audit – This object helps to monitor single sql server instance or database-level actions to monitor. We can setup multiple audit events for each SQL instance.

Server Audit Specification – The server audit specification belongs to SQL Server audit. This object is used to specify audit events at Instance level. This collects many server-level action groups raised by the Extended Events feature.

Database Audit Specification – This database server audit specification belongs to SQL Server audit. This object is used to specify audit events at database level. We need to create one database audit specification per database.

Target – The results of audit are sent to target. This target can be a file, windows system security log, windows application event log.

Steps to setup Auditing
1) Create New Audit object and assign name and target
2) Either you can setup Server Audit Specification (to monitor instance level actions) or you can setup Database Audit Specification (to monitor database-level actions)
3) By default Audit is in disable state, we need to enable audit specification
4) Later we can view the audit result in the target.

Using SSMS

1. Open SSMS – Security – Audit – Right Click – Select New Audit
2. Specify Audit name and target location of the audit  -- Click OK to create an audit
3. After setup SQL Server Audit, we need to specify Server Audit Specification
4. Open SSMS – Security – Server Audit Specification – New Server Audit Specification / New Database Audit Specification
5. Select required audit actions need to monitored and then Click on OK Button

Note – Initially Audits and Audit specifications are in disabled mode, first we need to enable it to start capturing the events. Eventhough you specified text file as target, we can’t directly read the text file using a text editior, we need to rightclick on Audit – select view audit logs, and from there you can export into a text file. Similarly if you select target as application log or security log then all these events will be logged into respective sections in the event viewer