SQL Server MVP Deep Dives- P19: Each year Microsoft invites all the MVPs from every technology and country to Redmond for an MVP Summit—all top secret—“don’t tweet what you see!” During the MVP Summit, each product team holds a series of presentations where they explain their technologies, share their vision, and listen to some honest feedback. | 674 Chapter 53 SQL Server Audit change tracking and change data capture ALTER SERVER AUDIT ServerAudit WITH STATE ON GO USE HR GO CREATE DATABASE AUDIT SPECIFICATION HRAudit FOR SERVER AUDIT ServerAudit ADD SELECT INSERT UPDATE DELETE ON BY public WITH STATE ON GO When reviewing audit information whether in a file or in the event log there is a variety of information available to you including the time of the action the session_id SPID of the user that performed the action the database server and object that was the target of the action and whether or not the action succeeded. For a full listing of the columns written to an audit row see the Books Online topic SQL Server Audit Records located at http en-us library . I was disappointed to see that host name and or IP address of the session_id is not recorded. This can be important information in some instances and is difficult to determine after the session has disconnected from the server. For example if SQL Authentication is enabled and the sa or another sysadmin password is commonly known then anyone can connect that way via their own machine and be relatively untraceable. Another important note here is that the type of action for example SELECT or ALTER is recorded but in the case of SELECT or DML queries none of the data involved is included. For example if you run the statement in listing 3 the event log entry will look like listing 4 I ve left out some of the columns . Listing 3 Updating the Employees table UPDATE SET Salary Salary WHERE EmployeeID 5 Listing 4 Event log entry for the UPDATE command in listing 3 Log Name Application User N A Event ID 33205 Audit event event_time 2008-10-05 18 14 action_id UP session_id 56 session_server_principal_name sa server_instance_name SENTINEL SQL2008 database_name HR schema_name dbo object_name Employees statement UPDATE dbo . Employees set Salary Salary @1 WHERE EmployeeID @2 Please purchase .
