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:
- If the object owners were not changed after creation, then schema owner will be the owner of the objects.
- 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')