Object owner

The concept of object owner in SQL 2000 is replaced by schema in SQL 2005. E.g. in dbo.myTable, the dbo is a schema name, not a database user or the object owner any more. SQL server 2005 still keeps the concept of object owner. By default, the owner of an object is the owner its schema. E.g. in table Sales.SaleHistory, the schema is Sales, the owner of the table is the owner of Sales.
To list owners for all schemas:
USE AdventureWorks
GO
SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.principal_id=dp.principal_id
GO
However users can change the owner of an object without changing its schema.
ALTER AUTHORIZATION ON [objectname] TO [a database principle]
The owner is saved in sys.objects table.
SELECT * FROM sys.objects WHERE type=’U’
The principal_id will NOT be NULL if the owner of an object is changed to other than its schema owner.
The following script lists the owner for all user defined tables:
SELECT o.object_id, o.name,
  CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)
   ELSE (SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.schema_id=o.schema_id and s.principal_id=dp.principal_id)
  END as Owner
FROM sys.objects o
WHERE type=’U’
Change Production.ProductSubcategory owner to guest:
ALTER AUTHORIZATION ON Production.ProductSubcategory TO guest
GO
SELECT o.object_id, o.name,
  CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)
   ELSE (SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.schema_id=o.schema_id and s.principal_id=dp.principal_id)
  END as Owner
FROM sys.objects o
WHERE type=’U’
GO
Change its owner back to schema owner:
ALTER AUTHORIZATION ON Production.ProductSubcategory TO schema owner
GO
Advertisements
This entry was posted in SQL server 2005. 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