Configuring Granular Settings for a Database Level Audit

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
Enable Server Audit


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);

Read Audit data

Fig 1.2

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.

Login as MyLogin and MyLogin2

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);

see if these actions have been logged to the binary file

Fig 1.4

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

Metadata information on the Server Audit and Audit specification\

Fig 1.5

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

check the extended events that are created by SQL server related to this audit

Fig 1.7

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

Server audit can be stopped using the ALTER SERVER AUDIT command

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles