SQL Server 2005 Displaying Object Owners
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')
Agile Methodology in Project Management
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
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
Detailed Forensic Investigation of Malware Infections – April 21, 2015
How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015
Write a Comment
See what people are saying...
Share your thoughts...
I am trying to create a minimum privilege (read only) role which can select from a few tables, through ODBC for an application. When I do this though and test the ODBC using EXCEL, I can see several sys views and also dm views and master db objects.
How can I tie all of these down without individually ‘revoking’ the select access to these on the role?
Can I create a role which by default will do this? See only those tables that I allow it to, and NOTHING else.
Thanks in advance.