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: