Understanding a sample SQL audit event

Raw Audit Event from SQL Server

event_time:2010-09-16 12:35:30.0787755
sequence_number:1
action_id:APRL
succeeded:true
permission_bitmask:0
is_column_permission:false
session_id:54
server_principal_id:260
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:7
class_type:RL
session_server_principal_name: ACMESP\Administrator
server_principal_name: ACMESP\Administrator
server_principal_sid:0
database_principal_name: dbo
target_server_principal_name: ACMESP\Administrator
target_server_principal_sid: 0
target_database_principal_name: John Smith
server_instance_name: SPDEV\SQL08ENT
database_name: AuditTest
schema_name:
object_name: Human Resources
statement: EXEC sp_addrolemember N'Human Resources', N'John Smith'
additional_information:
file_name=c:\sql audits\AuditAll_12633920-
FB34-4FAA-8F96-E9F8FED158A9_0_ 129276798828120000.sqlaudit
audit_file_offset=1536

On the right is sample SQL Server audit event.

The good news is that all the information is there; the bad news is that, whether you use the event log or binary files, the messages are quite cryptic. Here's what SQL Server is trying to tell you in this case:

At 12:35AM on 9/16/2010, ACMESP\Administrator added John Smith to the Human Resources role in the AuditTest database on SQL Server SPDEV\SQL08ENT.

Bear in mind that the fields you see to the right are the same fields and format used for the hundreds of different operations that can be performed in SQL Server - everything from backing up databases, creating logins, adding members to roles to inserting a row in a table or executing a stored procedure.

Everything is logged under a single event ID, 33205, with the same format as shown to the right.

This is one of the major reasons why we designed the LOGbinder for SQL Server collector to process SQL audit logs. See a list of the 300+ different event IDs and messages produced by LOGbinder for SQL Server.

Next:

 

Upcoming Webinars
    Additional Resources