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 the role PAYROLL
FRED's default schema is PAYROLL
Even 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:
the 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:
ALTER USER FRED WITH DEFAULT_SCHEMA = FRED
will it even attempt to resolve the 1-part name to fred.some_table.
I'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.
So 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.
This can have some interesting twists because you can GRANT other users the right to create objects in a schema you own:
GRANT CREATE TABLE TO ALICE
GRANT ALTER ON SCHEMA::FRED to ALICE
means ALICE can create tables in the FRED schema. But that's a subject for another day….