What is Auditing ?

Auditing is used for tracking and logging events on a single instance or individual database .Through server audit specifications server level events and database audit specifications for database level events are captured into event logs or audit files.

Components :

audit is the combination of several elements into a single package for a specific group of server actions or database actions. The components of SQL Server audit combine to produce an output that is called an audit, just as a report definition combined with graphics and data elements produces a report.

SQL Server Audit Server Audit Specification Database Audit Specification
SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope. The Database Audit Specification object also belongs to a SQL Server audit. You can create one database audit specification per SQL Server database per audit.
The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are atomic events occurring in the Database Engine. These actions are sent to the audit, which records them in the target. The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification. Audit events are the atomic actions that can be audited by the SQL Server engine. Audit action groups are predefined groups of actions.
Both are at the SQL Server database scope. These actions are sent to the audit, which records them in the target.

How is the SQL Server Audit works ?

  • A SQL Server Audit object can be written to by one Server Audit Specification and one Database Audit Specification per database.
  • A SQL Server Audit can belong to only one SQL Server instance, but there may be several SQL Server Audits within an instance.
  • A Server Audit Specification defines which server-level events will be captured and passed to the SQL Audit.
  • A Database Audit Specification defines which database-level events are captured and passed to the SQL Audit.
  • Both Server Audit Specifications and Database Audit Specifications can defi ne sets of events or groups to be captured. Event groups encapsulate a number of related events. Database actions include select, insert, update, and delete, and they capture the user context and the entire DML query.
  • The audited data includes user context information.
  • The SQL Server Audit sends all the captured events to a single target: a fi le, the Windows Security event log (not in Windows XP), or the Windows Application event log. The Management Studio SQL Audit UI includes a tool for browsing the audit logs.
  • SQL Server Audits, Server Audit Specifications, and Database Audit Specifications can all be created and managed either with Object Explorer or by using T-SQL.
  • SQL Server Audits, Server Audit Specifications, and Database Audit Specifications can all be enabled or disabled. They may be modified only while disabled. All are disabled by default when they are first created because that’s how Extended Events works.
  • SQL Server Audits, Server Audit Specifications, and Database Audit Specifications can all be managed by Policy-Based Management.

SQL Audits are serious. The SQL Server Audit object can be configured to shut down the server if the audit doesn’t function properly

Configuration Using Management Studio:

he SQL Server Audit feature can be set up using either T-SQL, or SQL Server Management Studio options.To configure the feature using SQL Server Management Studio:

  1. To create a SQL Server Audit object, expand the Security folder in Object Explorer
  2. Expand the SQL Server Logs folder
  3. Select New Audit

SA01

4.  In the Create Audit dialog, specify the audit name, audit destination, and path