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')

Videos You May Like

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

1 1373 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

Agile Methodology in Project Management

0 184 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

Creating Users and Managing Passwords in Microsoft Office 365

0 795 4

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.   For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365    

Write a Comment

See what people are saying...

    Share your thoughts...

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