Schemas, Users, and Objects – III

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 —

4 thoughts on “Schemas, Users, and Objects – III

  1. 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

  2. Hi Christian,

    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.

  3. Just tried the script on CTP 16 and they have resolved this issue; fred can no longer access fredstuff.table1 at the point above. Maybe Dan filing it as a bug has had an effect.

    I think it makes sense that fred can’t access fredstuff.table1. I mean, what if fred turned to the dark side?!

    Christian

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.