New Auditing Feature in SQL Server 2008
By : Kasim Wirama, MCDBA
In auditing area, SQL Server 2008 introduces Auditing feature for server and database. This audit feature together with existing C2 Audit Mode brings options for database administrator to provide information to auditor so that auditor can make sure the database could be auditing and stays in compliance as organization regulatory is stated. When you need flexible and custom audit on specific actions, targets, and associate database role, C2 Audit Mode doesn’t give you what you want because it will return all of them without expected flexibility. So it is worth to look at what audit feature could do in SQL Server 2008.
Audit feature in SQL Server 2008 has 2 types : server level and database level. Both of them are specified through transact sql command : CREATE SERVER AUDIT SPECIFICATION and CREATE DATABASE AUDIT SPECIFICATION respectively. Both of them references SERVER AUDIT object.
SERVER AUDIT object contains information about where audit information is saved to. Here is sample of SERVER AUDIT command : (set database context to MASTER database first).
use MASTER
GO
CREATE SERVER AUDIT [Server_Audit]
TO FILE
(
FILEPATH = N’C:\Audit\’,
MAXSIZE = 10mb,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = on
)
WITH
(
QUEUE_DELAY = 1000,
ON_FAILURE = SHUTDOWN,
AUDIT_GUID = ‘AD00A0B2-0178-41AB-B84D-6A20D2A31BEE’
);
Server_Audit is server audit name. Filepath parameter is folder name where audit file will be saved to, and each file size is 10mb with maximum 10 audit files as rollover files, you can reserve disk space as well with ON value otherwise just remove this option or set to OFF. Queue_delay is maximum number of milliseconds when a audit action is captured by SQL Server until its information is saved into C:\Audit folder; when you specify the value, it will work in asynchronous nature. ON_FAILURE = SHUTDOWN will shutdown SQL Server when server audit events couldn’t work as expected such as disk space full, or hardware failure. You can remove this option when you think that you don’t need to shutdown SQL Server in case server audit doesn’t work. AUDIT_GUID contains guid value, it is used in failover scenario when 1 database server fails because of some reason, the failover database server will take over the database job including server audit object,
Let’s take a look at server audit feature then followed by database audit feature.
As the name implies, server audit feature covers everything about server actions such as login attempt or login password changes, etc. Here is an example of server audit specification.
use MASTER
GO
CREATE SERVER AUDIT SPECIFICATION [login_audit]
FOR SERVER AUDIT Server_Audit
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON);
login_audit will listen for any failed login group events. If you want to add other events you need to stop the server audit specification first.
ALTER SERVER AUDIT SPECIFICATION [login_audit]
WITH (STATE = OFF);
Then you can add other events, for example I add SUCCESSFUL_LOGIN_GROUP here :
ALTER SERVER AUDIT SPECIFICATION [login_audit]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON);
You can drop event from login_audit with DROP command in place of ADD command similar when you add event to a server audit specification.
Now, I tell you about database audit specification. Here is the example of database audit specification.
use AdventureWorks
GO
CREATE DATABASE AUDIT SPECIFICATION [database_audit]
FOR SERVER AUDIT Server_Audit
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION _CHANGE_GROUP),
WITH (STATE = ON);
What if you just need to track insert, update, delete statement on table humanresources.employee by public database role? Here is the database audit specification command how to achieve this:
CREATE DATABASE AUDIT SPECIFICATION [tracking_dml_humanresources_employee_table_audit]
FOR SERVER AUDIT Server_Audit
ADD (INSERT,UPDATE,DELETE ON HumanResources.Employee BY [public])
WITH (STATE = ON);
Or if you want tracking dml changes on specific schema, it is possible to do so in database audit specification with this command for example :
CREATE DATABASE AUDIT SPECIFICATION [tracking_dml_humanresources_schema_audit]
FOR SERVER AUDIT Server_Audit
ADD (INSERT,UPDATE,DELETE ON SCHEMA::HumanResources BY [public])
WITH (STATE = ON);
Besides SCHEMA::, you are able to specify DATABASE:: and OBJECT::
As discussion closing for this topic, I let you know that besides specifying sql command, you can do by GUI at SSMS by navigating to SECURITY folder. Under the folder, you will find out Audits folder and Server Audit Specifications folder to manage your server audit and server/database audit specification respectively.