question

IT1 avatar image
IT1 asked

SQL 2008 audit

hi, I created an audit on database object change and when i dropped a table i got the following log in my application log. I just want to know how can i interpret reading the log wat action was performed and by whom assuming i dont already know that its because of a dropped table event_time:2011-05-13 01:23:36.2111800 sequence_number:0 action_id:AUSC succeeded:true permission_bitmask:0 is_column_permission:false session_id:65 server_principal_id:261 database_principal_id:0 target_server_principal_id:0 target_database_principal_id:0 object_id:0 class_type:A session_server_principal_name:XYZ123-PC\XYZ123 server_principal_name:XYZ123-PC\XYZ123 server_principal_sid:0105000000000005150000003ccfac2799b8a594cc21d315e8030000 database_principal_name: target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:XYZ123-PC database_name: schema_name: object_name: statement: additional_information: Audit-20110512-211323$A event enabled audit_event Thank you
sql-server-2008audit
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
This particular event is not the dropped table, it is the creation of your database audit (`action_id:AUSC`). You should have another event log entry after this one with `action_id:DR` for the `DROP TABLE`. When you find the entry with `action_id:DR` the "`statement:`" portion of the entry will have your `DROP TABLE` statement. A description of all the items in the log entry can be found in the `sys.fn_get_audit_file` entry in books online - [ http://msdn.microsoft.com/en-us/library/cc280765.aspx][1] You can get a complete list of valid `action_id` values and what they refer to with the following query: select * from sys.dm_audit_actions order by action_id [1]: http://msdn.microsoft.com/en-us/library/cc280765.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I tried looking for it but it seems that I get the audit value when i save it as a file but cant seem to get in the application or the security application log .... any reason y this would happen ... i am logged in as a admin
0 Likes 0 ·
Not sure about the application/security log access. Saving audit data to a file is the better way to go because you can easily query it directly with `fn_get_audit_file` or load it into the database for analysis, etc. When it's in the windows event logs, it's more difficult to work with.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.