Now, back to our regularly scheduled technical content. About schemas, users, and owners.

Although Ed originally created the table, since Fred is the schema owner, Fred owns to table. Ed can get ownership of the table in either of two ways.

1. Someone with authority can alter the table's owner
2. Someone with authority can give Ed "take ownership" permission on the table

Until Ed has "take ownership" permission, he does not and cannot "own" the table he just created.

There are two ways to tell who owns a table. If you know who the schema owner is, "select * from sys.tables" produces a column named principal_id. If the principal_id is NULL, then the table is owned by the schema owner. If the principal_id is not NULL, the table has a specific owner. The other way is to use the OBJECTPROPERTY function and look for the property 'OwnerId'. This gives the exact owner, whether or not it's the schema owner.

This matters because, if you change the schema owner, the owner of the tables with NULL in prinipal_id changes. The owner of "specific-owner" tables does NOT change. So if the schema owner changes, say, to DBO, then DBO owns all the tables in the schema. BUT does not own Ed's table.

This whole thing is made possible because of the interesting meld that had to happen between a SQL Server-specific feature (ownership chains) and a new SQL2003-compliant feature (separation of users and schemas). Is this clear as a bell, now?

Just in case you don't believe it, code below (picks up where other code left off):

— snip (when I left off, I was Ed)
— ed cannot get ownership of table
— this fails
alter authorization on object::fredstuff.edtab to ed
go

4 Responses to Schemas, Users, and Objects – III

In the following code from the snippet, why is fred able to access fredstuff.table1? He is not the table owner, he is no longer the schema owner and he was never assigned select permissions on the table.
Cheers

setuser ‘fred’
go
— no access for fred on this table
select * from fredstuff.edtab
— access for fred on this table
select * from fredstuff.table1
go

Not sure as to the motivation behind this, but, even if the schema changes hands, the original owner of the schema is still able to access objects. I think Dan may be filed this as a bug; it’s an observed behavior.