Database Audit Specification
The Database Audit Specification object is also part of an SQL Server Audit. A DBA can create one database audit specification per SQL Server database per audit. The database audit specification collects database level audit actions raised by the Extended Events feature. You can also 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. However, Audit action groups are predefined groups of actions and both are at the SQL Server database scope. These actions are sent to the audit which records them in the target. Users in the db_owner role can tamper with any audit specifications in a database.
Target
The results of an audit are sent to a target which can be a file, Windows Security event log, or the Windows Application event log. The important thing to be noted is that writing to the Security log is not available on Windows XP. A DBA has to review the logs and archive them periodically to make sure that the target has sufficient space to write additional records. If a DBA wants to write it to the windows security log then the SQL Server service account needs to be added to the Generate Security Audit Policy. By default, Local System, Local Service and Network Service are part of this policy. The settings can be configured by using the security policy snap in secpol.msc
How to use SQL Server Audit
A DBA can use SQL Server Management Studio (SSMS) or Transact SQL to define an audit. Once the audit is created it needs to be enabled as by default SQL Server doesn’t enable it. Until the audit is enabled target will not receive entries. You can use the Event Viewer utility in Windows to read the windows security or windows application event log. You can use Log File Viewer utility in SQL Server Management Studio or the FN_READ_AUDIT_FILE inbuilt function in SQL Server to read the target file.
The process for creating and using the audit feature is:
- Create an audit and define the target where audit information will be captured
- You can create either a server or database audit specification
- Enable the audit specification
- Finally, enable the audit. By default SQL Server doesn’t enable the audit when created
- Analysis the audit events which were captured by using the Windows Event Viewer, Log File Viewer or by using the inbuilt function FN_READ_AUDIT_FILE
Conclusion
The Audit feature of SQL Server 2008 allows Database Administrators to enable Database and Server level audits on SQL Server Instances.
]]>