How to detect the owner of the database objects

Prior to SQL Server 2005, if you create objects in a schema, the owner of the object is the user who created it. However, this behavior was change in SQL Server 2005 and later versions, by default, if you create objects in a schema, the schema owner will be the owner of the objects, not you (who created the objects). In both cases, we can change the owner of the objects using ALTER AUTHORIZATION statement.

So, here comes another question, how can we know the owner of the objects in SQL Server 2005 and later versions. Here are two rules:

  1. If the object owners were not changed after creation, then schema owner will be the owner of the objects.
  2. If the object owners were changed after creation, then we need to find out that database user who is the new owner of the objects.

Fortunately, in the sys.objects system catalog view, the principal_id column will indicate this. If the principal_is is NULL, the object is owned by the schema owner. If not NULL, this principal is the owner of the object. Here is a script that we can detect the owner of a specific object:

-- If the principal_id is NULL, which means the object owner is not changed 
-- after creation so that the owner will be the schema owner. Otherwise, 
-- the object owner will be the user whose principal_id is 
-- equal principal_id in sys.objects table
SELECT 
	o.name AS OBJECT_NAME,
	CASE 
		WHEN o.principal_id IS NULL THEN dp2.name
	ELSE  dp.name END AS Object_Owner
FROM 
	sys.objects AS o
	LEFT JOIN sys.schemas AS s 
	ON o.schema_id = s.schema_id
	LEFT JOIN sys.database_principals AS dp
	ON o.principal_id = dp.principal_id
	INNER JOIN sys.database_principals AS dp2
	ON s.principal_id = dp2.principal_id

WHERE o.object_id = OBJECT_ID(N'input_object_name_here')

Hope this helps.
Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s