Like This Blog 1

Added by Jeff Jones September 27, 2006

I got a question in class to as to how to figure out the owner of a stored procedure or a view. After looking at the sys.all_objects system view, saw that the principal_id column had a NULL in each row. SQL Server 2000 BOL says that for Views and Store Procedures the Principal_ID column value will be NULL if the object is owned by the schema owner. If the object ownership has been changed with the ALTER AUTHORIZATION statement, it will have a value other than NULL.

So I wrote a query that will display the object owner for views and stored procedures whether it is owned by the schema owner (whoever that is) or is owned by an account other than the schema owner.

Here is the query:

SELECT s.name + '.' + o.name AS ObjectName
— Display schema owner or overidden owner
, COALESCE(p.name, p2.name) AS OwnerName
FROM sys.all_objects o
LEFT OUTER JOIN sys.database_principals p
ON o.principal_id = p.principal_id
LEFT OUTER JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT OUTER JOIN sys.database_principals p2
ON s.principal_id = p2.principal_id
WHERE o.type IN ('V', 'P')
AND s.name NOT IN ('sys', 'INFORMATION_SCHEMA')