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

Agile Methodology in Project Management

0 163 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 708 3

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    

How to clone a Windows Server 2012 or 2012 R2 Domain Controller

3 1608 3

One of the coolest new features in Window Server 2012 and Windows Server 2012 R2 is the ability to clone a Domain Controller. In the past, if we had virtualized Domain Controllers and we actually took a snapshot of it and then rolled back to that snapshot, it would break the logon service on that … Continue reading How to clone a Windows Server 2012 or 2012 R2 Domain Controller

Write a Comment

See what people are saying...

    Share your thoughts...

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