\nSeparation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED.\n<\/p>\n
\nFRED is the owner of a schema named FRED
\nFRED is a memeber of the role PAYROLL
\nFRED's default schema is PAYROLL\n<\/p>\n
\nEven though FRED is the owner of a schema named after him, resolving a one-part object name like some_table uses a simple_algorithm: look in default schema first, then look in dbo schema. Even if you own another schema, only your default schema is used to resolve a 1-part name. BTW, the "sys" metadata schema complicates this a little bit, but I'm ignoring that for now. So for FRED, if the following tables exist:\n<\/p>\n
\nfred.some_table
\ndbo.some_table
\npayroll.some_table\n<\/p>\n
\nthe statement "select * from some_table" executed by FRED, selects payroll.some_table. If payroll.some_table is dropped, it selects dbo.some_table. If FRED leaves the payroll department (and is removed from the role), it still selects dbo.some_table. Only when you do:\n<\/p>\n
\nALTER USER FRED WITH DEFAULT_SCHEMA = FRED\n<\/p>\n
\nwill it even attempt to resolve the 1-part name to fred.some_table.\n<\/p>\n
\nI'd always wondered about how this affected ownership chains, too. A simplistic explanation of these is: authorization of a database object is only checked when an ownership chain is broken. So if procedure A uses table B, authorization is only checked if the owner of procedure A is different from the owner of table B.\n<\/p>\n
\nSo does user-schema separation change this? Is "ownership" defined as the user who owns the object or as the schema the object lives in? This is an easy one also…owner is still not object's owner, NOT the schema the object lives in.\n<\/p>\n
\nThis can have some interesting twists because you can GRANT other users the right to create objects in a schema you own:\n<\/p>\n
\nGRANT CREATE TABLE TO ALICE
\nGRANT ALTER ON SCHEMA::FRED to ALICE\n<\/p>\n
\nmeans ALICE can create tables in the FRED schema. But that's a subject for another day….<\/p>\n","protected":false},"excerpt":{"rendered":"
Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED. FRED is the owner of a schema named FRED FRED is a memeber of […]<\/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-1003","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2005","category-sql-server-schemas"],"yoast_head":"\n