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);
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.
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);
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
\
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
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
Fig 1.7
Conclusion
This article has illustrated how to create database level audit at a granular level and retrieve the audit records.