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: event enabled
Thank you
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
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.
