SQL Server 2008 introduces a new auditing capabilities that can be invaluable to the DBA tasked with producing detailed audits that track database usage.
Auditing can be implemented at the server and database levels, enabled on individual database objects and saved to different formats, such as binary files or the Windows Application log.
To set up auditing in SQL Server 2008, follow these steps:
- Create a SQL Server audit for the specific instance of SQL Server 2008.
- Create a server audit specification, a database audit specification or one of each.
- Enable the SQL Server audit.
- View the audited data.
To understand the setup and management of auditing in SQL Server 2008, it is best to start with the three main objects which describe audits.
The Server Audit object describes the target for audit data, plus some top-level configuration settings. Think of a Server Audit object as the declaration of the audit sink, or destination. This destination can be a file, the Windows Application log, or the Windows Security log. The allowable delay for writing events to the destination can also be configured on this object. Note that the Server Audit object contains no information about what is being audited – just where the audit data is going. Multiple Server Audit objects can be defined with each object being specified and operational independent from one another.
The Server Audit Specification object describes what to audit. As its name suggests, this object is focused on server instance-wide actions. A server audit specification is associated with a server audit in order to define where the audit data is written. There is a one-to-one relationship between the Server Audit Specification object and the Server Audit object.
The Database Audit Specification also describes what to audit. But, as its name suggests, this object is focused on actions which occur in a specific database. Where the audit data is written is defined by the association of a database audit specification with a Server Audit object. Each database audit specification can be associated with only one server audit. A server audit, for its part, can be associated with only one database audit specification per database.
The following example shows the creation of an audit named MCIT_Audit, which will write to a file with no delay, and force the server to shut down in the event of a failure to write to the file.
CREATE SERVER AUDIT PCI_Audit TO FILE (FILEPATH = ’F:\AuditLogs\’, MAXSIZE = 1GB, MAX_ROLLOVER_FILES = 80) WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN)
This server audit is disabled initially.
The server audit can be modified after creation by using the ALTER SERVER AUDIT statement. The following example changes the destination to the Windows Application log and relaxes the queue delay and failure settings.
ALTER SERVER AUDIT PCI_Audit TO APPLICATION_LOG WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
The ALTER SERVER AUDIT statement is also used to enable or disable a server audit.
ALTER SERVER AUDIT PCI_Audit WITH (STATE = ON)
In SQL Server Management Studio, server audits are created and managed in the Audits folder, which is under the server-level Security folder.
To create a server audit specification
Lets see What is AUDIT ACTION TYPE
Audits can have the following categories of actions:
- Server-level. These actions include server operations, such as management changes and logon and logoff operations.
- Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.
- Audit-level. These actions include actions in the auditing process.
- Specific Database > Security > Server Audit Specification.
Limitations of SQL Server Audit
While SQL Server Audit includes many nice features, it also has some drawbacks that should be considered :
While SQL Server Audit takes up less physical resources than SQL Trace-based auditing options, it still uses SQL Server resources, and the more detailed your auditing, the more resources that are used. Because of this, it may not be practical to use SQL Server Audit on very busy OLTP servers, especially if they are already experiencing hardware bottlenecks.
SQL Server Audit is instance-based. In other words, there is no easy way to manage SQL Server Audit on all the SQL Server instances in your organization from a centralized location, unless you create your own method using scripts.
Audit data is stored either in a file, or as part of the operating system’s event logs. If you want to be able to analyze and report on this data, you will have to manually import it into your own database. In addition, DBAs will have to manually archive or delete old audit data.
There is no built-in reporting, other than looking at the events in the Log Viewer, assuming that is where you store the audit data. For effective reporting, you will have to create your own reports, most likely using SQL Server Reporting Services.
To overcome this limitation you should use an excellent tool like Apex to perform auditing with all feature that limited in SQL Auditing. Also Should you share with us the Auditing tools that you are using in your organization.