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.