\nThe 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:\n<\/p>\n
\n1. Alter the authorization on Ed's table so that it is owned by Ed.
\n (Interesting aside, can Ed do this himself?)
\n2. Create another table (using either Fred or Ed) in the schema.
\n Call it "fredstuff.table1" (my creativity for making sample names is legendary)
\n3. Alter the schema so that it's owned by another user (say, dbo)\n<\/p>\n
\nWho owns each table now? How can you tell?\n<\/p>\n
\nBTW, why does this matter? Because ownership chains go by object *owners*, not by schemas.\n<\/p>\n
\n— snip —
\ncreate database test
\ngo\n<\/p>\n
\nuse test
\ngo\n<\/p>\n
\n— make two users
\n— fred has a default schema, ed does not
\ncreate user fred for login fred with default_schema = fredstuff
\ncreate user ed for login ed
\ngo\n<\/p>\n
\n— create the schema for fred
\ncreate schema fredstuff authorization fred
\ngo\n<\/p>\n
\n— fred and ed can create tables
\n— ed can only create tables in fred's schema
\ngrant create table to fred,ed
\ngrant alter on schema::fredstuff to ed
\ngo\n<\/p>\n
\nsetuser 'ed'
\ngo\n<\/p>\n
\n— ed creates a table in fred's schema
\n— who is the owner?
\ncreate table fredstuff.edtab (id int)
\ngo\n<\/p>\n
\n— fred (schema owner) is the owner. not ed.
\n— ed cannot even SELECT against the table he just created, this fails
\nselect * from fredstuff.edtab
\ngo
\n— snip —<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,28,34],"tags":[],"class_list":["post-989","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2005","category-sql-server-schemas"],"yoast_head":"\n