\nNow, back to our regularly scheduled technical content. About schemas, users, and owners.\n<\/p>\n
\nAlthough 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.\n<\/p>\n
\n1. Someone with authority can alter the table's owner
\n2. Someone with authority can give Ed "take ownership" permission on the table\n<\/p>\n
\nUntil Ed has "take ownership" permission, he does not and cannot "own" the table he just created.\n<\/p>\n
\nThere 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.\n<\/p>\n
\nThis 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.\n<\/p>\n
\nThis 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?\n<\/p>\n
\nJust in case you don't believe it, code below (picks up where other code left off):\n<\/p>\n
\n— snip (when I left off, I was Ed)
\n— ed cannot get ownership of table
\n— this fails
\nalter authorization on object::fredstuff.edtab to ed
\ngo\n<\/p>\n
\n— back to dbo
\nsetuser
\ngo\n<\/p>\n
\n— dbo can give the table to ed
\n— alter authorization on object::fredstuff.edtab to ed
\n— go\n<\/p>\n
\n— or dbo can give ed 'take ownership' permission
\ngrant take ownership on fredstuff.edtab to ed
\ngo\n<\/p>\n
\nsetuser 'ed'
\ngo\n<\/p>\n
\n— now this works for ed, because he has 'take ownership'
\nalter authorization on object::fredstuff.edtab to ed
\ngo\n<\/p>\n
\n— now ed can SELECT the table
\nselect * from fredstuff.edtab
\ngo\n<\/p>\n
\n— ed creates another table in the schema
\ncreate table fredstuff.table1 (id int)
\ngo\n<\/p>\n
\nsetuser
\ngo\n<\/p>\n
\n— note that edtab has a principal_id (ed's)
\n— note that table1 (owned by schema owner) has NULL principal_id
\nselect * from sys.database_principals
\nselect * from sys.tables
\ngo\n<\/p>\n
\n— owned by 'fred' (schema owner)
\nselect objectproperty(object_id('fredstuff.table1'), 'OwnerId')
\n— owned by 'ed'
\nselect objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
\ngo\n<\/p>\n
\nsetuser 'fred'
\ngo\n<\/p>\n
\n— so can fred SELECT both tables
\n— because fred is the schema owner
\nselect * from fredstuff.edtab
\nselect * from fredstuff.table1
\ngo\n<\/p>\n
\nsetuser
\ngo\n<\/p>\n
\nalter authorization on schema::fredstuff to dbo
\ngo\n<\/p>\n
\nsetuser 'fred'
\ngo
\n— no access for fred on this table
\nselect * from fredstuff.edtab
\n— access for fred on this table
\nselect * from fredstuff.table1
\ngo\n<\/p>\n
\nsetuser
\ngo
\nsetuser 'ed'
\ngo
\n— access for ed, he's still the owner
\nselect * from fredstuff.edtab
\n— never had access to this table
\nselect * from fredstuff.table1
\ngo\n<\/p>\n
\nsetuser
\ngo\n<\/p>\n
\n— note that edtab has a principal_id (ed's)
\n— note that table1 (owned by schema owner) has NULL principal_id
\nselect * from sys.tables
\ngo\n<\/p>\n
\nselect * from sys.database_principals
\n— owned by 'dbo' (schema owner), this changed
\nselect objectproperty(object_id('fredstuff.table1'), 'OwnerId')
\n— owned by 'ed', this did not change\n<\/p>\n
\nselect objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
\ngo\n<\/p>\n
\n— snip —<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/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-987","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2005","category-sql-server-schemas"],"yoast_head":"\n