Tuesday, August 19, 2008

Suggestion for Extended Events

As you can probably tell, I have been completely focused on Extended Events and learning/documenting how to use them for the last few weeks.  One thing I noticed is that the new Audit functionality in SQL 2008 actually runs on the Extended Events Engine.  This morning a friend chatted me by IM and asked about sp_rename and how the Audit catches it.  It is actually picked up by the Audit as an ALTER of the object.  However, they also needed to know the HostName that originated the request, which is not available in the Audit.  This is because it is also not available as an Action in Extended Events.  This would be a valuable piece of information to have and I have filed this as a suggestion with the SQL Server connect site:

SQL 2008 Extended Events Addition

There is however, a workaround that I provided my friend.  You can create a DDL LOGON trigger that uses the session_id to get the HostName if it exists from the sys.dm_exec_sessions DMV and store it to an Audit table.  Then you can correlate the Audit Event Time, and session_id with the Audit tables Session_id and event time for the Logon to know what HostName was logged onto the Session_ID.  Kind of a hack work around but it will get the job done.

No comments:

Post a Comment