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.

Difference between SQL 2000 and SQL 2005 system Views

Part of my job is answering questions for internal and external audits.  Today one came in that made me have to break out some code and do some investigation.  One of the queries I provided had conflicting data with the results from another query.  Basically the first query said that there were objects owned by a database user, while the second said that the database user can't create objects.  This might seem trivial since it is possible that the user once had rights to create objects, only the objects were created since the last audit, and the user never had create rights on the database.

The problem wasn't that the user created objects, it was that the queries being used were returning invalid information in SQL 2005 where they worked correctly in SQL 2000.  As a part of the upgrade to 2005, a new schema was created for DBA use in one of our databases where I copy tables (using SELECT * INTO DBA.TableName_DR_#### FROM TableName) before making changes to them in a Deployment Request, so that there is a rapid rollback point for changes being made in the event of a problem.  In SQL 2000 we would run code like the follow:

SELECT sysobjects.name AS [object Name], sysusers.name AS Owner, CASE 
WHEN sysobjects.xtype = 'S' THEN 'System Table' 
   WHEN sysobjects.xtype = 'P' THEN 'Stored Procedure' 
   WHEN sysobjects.xtype = 'U' THEN 'User Table' 
   END AS Type
FROM sysobjects 
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Where sysobjects.xtype in ('S','P','U')
Order by sysobjects.xtype desc

With schemas this pulls back incorrect users as the owner of objects.  Instead in SQL 2005, the query should look like this:

select o.name, s.name [schema], p.name [schema owner], o.type_desc [Type]
from sys.objects o 
join sys.schemas s on o.schema_id = s.schema_id
join sys.database_principals p on s.principal_id = p.principal_id

In some cases like the above the output from the compatibility is not equivalent.