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
-- back to dbo
setuser
go
-- dbo can give the table to ed
-- alter authorization on object::fredstuff.edtab to ed
-- go
-- or dbo can give ed 'take ownership' permission
grant take ownership on fredstuff.edtab to ed
go
setuser 'ed'
go
-- now this works for ed, because he has 'take ownership'
alter authorization on object::fredstuff.edtab to ed
go
-- now ed can SELECT the table
select * from fredstuff.edtab
go
-- ed creates another table in the schema
create table fredstuff.table1 (id int)
go
setuser
go
-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.database_principals
select * from sys.tables
go
-- owned by 'fred' (schema owner)
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed'
select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go
setuser 'fred'
go
-- so can fred SELECT both tables
-- because fred is the schema owner
select * from fredstuff.edtab
select * from fredstuff.table1
go
setuser
go
alter authorization on schema::fredstuff to dbo
go
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
setuser
go
setuser 'ed'
go
-- access for ed, he's still the owner
select * from fredstuff.edtab
-- never had access to this table
select * from fredstuff.table1
go
setuser
go
-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.tables
go
select * from sys.database_principals
-- owned by 'dbo' (schema owner), this changed
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed', this did not change
select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go
-- snip --