SQL Server 2005 Displaying Object Owners

Home > Blogs > SQL Server > SQL Server 2005 Displaying Object Owners

SQL Server 2005 Displaying Object Owners

Like This Blog 1 Jeff Jones
Added by 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')

Videos You May Like

A Simple Introduction to Cisco CML2

0 3660 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

How to Build in a PSMethod to your PowerShell Code

0 66 0

In this video, PowerShell instructor Jason Yoder shows how to add Methods (PSMethod) to your code using free software that’s added into the PSObject. For instructor-led PowerShell courses, see our course schedule. Microsoft Windows PowerShell Training Download the Building Methods PowerShell script</a> used in this video. <# ╔══════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ Building Methods ║ ╟──────────────────────────────────────────────────────────────────────────────╢ … Continue reading How to Build in a PSMethod to your PowerShell Code

Configuring Windows Mobility Center and How to Turn it On and Off

1 1404 1

Video transcription Steve Fullmer: In our Windows training courses, we often share information about the Windows 8.1 Mobility Center. Mobility Center was introduced for mobile and laptop devices in Windows 7. It’s present and somewhat enhanced in Windows 8. Since we don’t have mobile devices in our classrooms, I decided to take a little bit … Continue reading Configuring Windows Mobility Center and How to Turn it On and Off

Write a Comment

See what people are saying...

    Share your thoughts...

    Please fill out the comment form below to post a reply.