Configuring Granular Settings for a Database Level Audit

February 9, 2010

Learn how to create a database level audit at a granular level and retrieve the audit records in this 9-step tutorial.

"Auditing made easy by Microsoft SQL Server 2008" illustrated how to create, and test a database level audit, as well as how to retrieve the audit records. Now we move on to creating a database level audit at a granular level and then retrieve the audit records.

Step1: Create Server Audit

First, let's create a Server Audit Object using the following command.

CREATE SERVER AUDIT MyDB_Database_Granular_Audit 
TO FILE (FILEPATH='C:\SQLAudit\', MAXSIZE = 5MB, 
MAX_ROLLOVER_FILES = 100, RESERVE_DISK_SPACE = ON)

Note: Compliance_Database_Audit is the name of the Server Audit. This command allocates 5MB of disk space for this audit. When the 5MB file is filled up, it automatically rolls over to another file to the maximum number of 100 Files. When the audit restarts the next time, it evaluates the number of files and deletes any older files.

Step 2: Enable Server Audit

By default, the server audit is disabled. Enable the server audit using the following command.

ALTER SERVER AUDIT MyDB_Database_Granular_Audit with (STATE = ON)

Note: Once the audit starts, the OS allocates 5MB disk space for the audit file. Refer to Fig 1.1.

Click for larger image

Fig 1.1

Step 3: Add the Database Audit specification

Database audit specification is the list of action groups that Server Audit will audit.

Let's add a couple of actions when creating the Audit specification.

use MyDB
go
CREATE DATABASE AUDIT SPECIFICATION MyDB_Database_Granular_Audit_SPEC
FOR SERVER AUDIT MyDB_Database_Granular_Audit
    ADD (INSERT ON MyDB.dbo.MyTable  BY MyLogin),
    ADD (DELETE ON MyDB.dbo.MyTable  BY MyLogin2)

Note: Compliance_Database_Audit_Spec is the name of the Server Audit specification.

Step 4: Update Server Audit specification

If you want to add action groups to the existing database audit specification, you could use the following statement.

ALTER DATABASE AUDIT SPECIFICATION MyDB_Database_Granular_Audit_SPEC
    ADD (UPDATE ON MyDB.dbo.MyTable  BY MyLogin,MyLogin2)

Note: You could skip step 4 if you already listed all of the action groups in Step 4

Step 5: Enable Server Audit specification

Enable the Audit specification by using the following command.

use MyDB
go
ALTER DATABASE AUDIT SPECIFICATIOn MyDB_Database_Granular_Audit_SPEC  WITH (STATE = ON)

Step 6: Read Audit data

Execute the following command to read the AUDIT data from the binary file. Refer to Figure 1.2

SELECT event_time, action_id, succeeded, session_id, session_server_principal_name, target_server_principal_name, 
	target_database_principal_name, database_name, database_principal_name, schema_name, object_name, 
	statement FROM sys.fn_get_audit_file ('C:\SQLAudit\MyDB_Database_*.sqlaudit',default,default);

Step 7: Test Audit

We have the following action added to the Audit specification.

INSERT
UPDATE
DELETE

Now let's test the action group by executing the following TSQL Command.

Use MyDB
go
GRANT INSERT,DELETE, UPDATE on MyTable to MyLogin,MyLogin2 
go

Login as MyLogin and MyLogin2 and insert, update and delete some rows. Refer to Fig 1.3.

use MyDB
go
Insert into mytable select 1
Insert into mytable select 2
Insert into mytable select 3
Insert into mytable select 4
Insert into mytable select 5
Update mytable set id =25 where id =2
delete mytable where id = 5

Now let us see if these actions have been logged to the binary file under c:\SQLAudit, by executing the command. Refer to Fig 1.4.

SELECT event_time,statement, action_id, succeeded, session_id, session_server_principal_name, 
target_server_principal_name, target_database_principal_name, database_name, database_principal_name, 
schema_name, object_name from sys.fn_get_audit_file ('C:\SQLAudit\MyDB_Database_*.sqlaudit',default,default);

Step 8: Check the Audit metadata

Metadata information on the Server Audit and Audit specification can be viewed using the following commands.

SELECT * FROM sys.server_audits
SELECT * FROM sys.Database_audit_specifications

You also check the extended events that are created by SQL server related to this audit by using the following Dynamic management view command. Refer to Fig 1.6.

select s.name as [session name], se.event_name as [event name], p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s 
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid 
join sys.dm_xe_session_targets t on s.address=t.event_session_address

Step 9: Stop Server AUDIT

Server audit can be stopped using the ALTER SERVER AUDIT command a shown below.

use master
go
ALTER SERVER AUDIT  MyDB_Database_Granular_Audit  with (STATE = OFF)

Note: When the audit is stopped, SQL Server releases the free space on the audit files to the Operating System. Refer to Fig 1.7

Conclusion

This article has illustrated how to create database level audit at a granular level and retrieve the audit records.

» See All Articles by Columnist MAK