Schemas, Users, and Objects – II

The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following:

1. Alter the authorization on Ed's table so that it is owned by Ed.
   (Interesting aside, can Ed do this himself?)
2. Create another table (using either Fred or Ed) in the schema.
   Call it "fredstuff.table1" (my creativity for making sample names is legendary)
3. Alter the schema so that it's owned by another user (say, dbo)

Who owns each table now? How can you tell?

BTW, why does this matter? Because ownership chains go by object *owners*, not by schemas.

— snip —
create database test

use test

— make two users
— fred has a default schema, ed does not
create user fred for login fred with default_schema = fredstuff
create user ed for login ed

— create the schema for fred
create schema fredstuff authorization fred

— fred and ed can create tables
— ed can only create tables in fred's schema
grant create table to fred,ed
grant alter on schema::fredstuff to ed

setuser 'ed'

— ed creates a table in fred's schema
— who is the owner?
create table fredstuff.edtab (id int)

— fred (schema owner) is the owner. not ed.
— ed cannot even SELECT against the table he just created, this fails
select * from fredstuff.edtab
— snip —

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.