Here's another blog posting to answer a question from over a month ago.

With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA named after the user and assign that SCHEMA as the user's default schema. Question was, "does sp_dropuser do the right thing and drop the schema named after the user?"

Short answer is "yes, it does". Pretty easy to prove. Actually, sp_adduser can eventually call sp_grantdbaccess and sp_dropuser eventually can call sp_revokedbaccess, so...

create login bob with password = 'A^#DNEfdfhkWD#*iubdwc )000ks1'
go

use adventureworks
go

sp_grantdbaccess 'bob'
go

select * from sys.schemas  -- bob schema is there
go

sp_revokedbaccess 'bob'
go

select * from sys.schemas  -- bob schema is gone
go

The more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:

-- creates schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_grantdbaccess'))

-- drops schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_revokedbaccess'))

Sure enough, there is nicely commented code in sp_revokedbaccess to drop a schema. BUT rather than using the DROP SCHEMA DDL statement, it looks like this:

EXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)

Hmmm....

When I discuss separation of users and schemas in SQL Server 2005, I usually think of ownership chains (that follow the object owner which is always the schema owner unless you change it) or object resolution (which now goes: special name, then default_schema/procedure schema, then DBO). Another item that now follows schemas rather than users is query plan reuse with 1-part object names. Create two users:

-- logins already created
CREATE USER bob FOR LOGIN bob
CREATE USER mary for LOGIN mary
GRANT SELECT ON authors TO bob, mary

The query plan for the following query (executed by bob or mary)

USE pubs
GO
SELECT * FROM authors -- note the 1-part object name

would not normally be reused for both bob and mary. In SQL Server 2005, it can be reused if bob and mary have the same default schema. BTW, this is adhoc SQL, rules differ in procedural objects like stored procs. To see the reason for the query reuse, you can obtain the plan handle (reference this blog entry), but leave out querying the plan itself because you don't need it here) and pass it in to the sys.dm_exec_plan_attributes dynamic management function. This function shows the attributes of the plan and includes a second column, is_cache_key. If the cache_keys match, the plan is reused.

One of the rows produced by sys.dm_exec_plan_attributes is named user_id. Interestingly, for the query plan for bob or mary against SELECT * FROM authors, the user_id is 1. DBO. Hmmm... User_id is a misleading name for this column. It's not the user_id of the user who executed the query (and caused the plan to be created) but that user's DEFAULT_SCHEMA's owner's user_id. Looking at the CREATE USER DDL statements above, because I didn't specify a DEFAULT_SCHEMA, both users' DEFAULT_SCHEMA is DBO. So, for adhoc SQL, all other things being equal, the plan will be resued for two users as long as they have the same DEFAULT_SCHEMA. The rules for query plan reuse follow the rules for object resolution, which makes sense, once you think about it.

Of course, now that you know this, ALWAYS use 2-part names. User_id with 2-part names' plans has a special value of -2 which ensures the plan is resued regardless of DEFAULT_SCHEMA of the user who executes it.

I've answered a few questions lately on setting up SqlDependency or ASP.NET SQL Server dependency with SQL Server 2005. Folks have gone by the instructions on the DataWorks Weblog posting and still receive the error "either schema dbo does not exist or you do not have permission to access it". This is caused by the separation or users and schemas in SQL Server 2005.

The instructions don't show creating 'startUser' (the principal that creates procedures, queues, and services), so folks create it, using the new DDL, like this:

CREATE LOGIN startUser WITH PASSWORD = 'SomeStrongPW1'
CREATE USER startUser FOR LOGIN startUser

Problem is, CREATE USER doesn't assign a default database schema (its not supposed to) and when startUser attempts to create database objects, it creates them in the "default default_schema" which is dbo. The quick fix is to create a schema for (owned by) the user and make that schema its default schema.

CREATE SCHEMA startUserSchema AUTHORIZATION startUser
ALTER USER startUser WITH DEFAULT_SCHEMA = startUserSchema

A better alternative might be to create a database role for this function and create the default schema owned by the role. Then add startUser to the role. You still have to alter the user's default_schema in this case, because database roles cannot have default schemas. Roles cannot have default_schemas themselves because if one user was a member of 3 different roles and each role had a different default_schemas which one would "win"?

A less attractive (actually unattractive) alternative is to give startUser CREATE (actually ALTER) privilege on the DBO schema.

GRANT ALTER ON SCHEMA::dbo to startUser

DON'T do this, you've just given startUser much more privilege than it really needs.

Three more comments:
1. If you used sp_adduser instead of CREATE USER, you "got lucky". For backward compatibility sp_adduser actually does:

CREATE USER startUser WITH DEFAULT_SCHEMA = startUser
GO
-- create schema must be first statement in the batch
CREATE SCHEMA startUser AUTHORIZATION startUser

When/if that backward compatibility mode is removed, your luck runs out.

2. With the new separation of users and schemas granting CREATE TABLE permission doesn't give the user enough to create a TABLE, nowadays. The user also needs a *container* to create tables (or other database objects) in. The user needs a database schema. Resist the temptation to make a schema for the user; rather make a schema for a role the user is a member of. The fact that schemas can be owned by a role is one of their best features.

3. Technically, Service Broker SERVICEs (as well as MESSAGE_TYPEs and CONTACTs) don't live at schema scope. They live at database scope, so you don't need a schema for them. CREATE privilege is enough. But QUEUEs (and most database objects) do live at schema scope. QUEUEs are just tables with special semantics, after all.

In answering a question about schemas, users, and objects (search on "schemas" to see the blog series I, II, III), I realized I never posted the portion about object resolution. Here goes.

When SQL Server resolves a one-part object name, the object resolution is slightly different if you're inside a stored procedure.

If batch or dynamic SQL:
1. Look in 'sys' schema for system objects
2. Look in user's deafult schema
3. Look in dbo schema

Note that if the user owner 100 schemas, SQL Server 2005 only looks in the default schema. If the user's  default schema isn't named after him, SQL Server 2005 never looks for name.object either.

If procedural code:
1. Look in 'sys' schema for system objects
2. Look in *procedure* schema
3. Look in dbo schema

Note that, in a stored procedure for example, SQL Server 2005 won't look in the user's default schema. Only the schema where the procedure lives.

Here's a code snippet that (hopefully) make this clearer:

create login ed with password='StrongPW!'
create user ed for login ed with default_schema = edstuff
go
-- default
create schema edstuff authorization ed
go
-- named after ed
create schema ed authorization ed
go
-- another schema for procs
create schema edprocs authorization ed
go
grant create table to ed
grant create procedure to ed
go

execute as user='ed'
create table edtable (id int, description varchar(100))
create table ed.edtable (id int, description varchar(100))
create table edprocs.edtable (id int, description varchar(100))
go
insert edtable values(1, 'im in edstuff')
insert ed.edtable values(2, 'im in ed')
insert edprocs.edtable values(3, 'im in edprocs')
go
-- procedure not in default schema, but in edprocs
create procedure edprocs.geted
as
select * from edtable
go

-- i'm in edstuff
select * from edtable
go

-- i'm in edprocs
execute edprocs.geted
go

drop table edprocs.edtable
-- invalid object name 'edtable'
execute edprocs.geted
go

drop table edstuff.edtable
-- invalid object name 'edtable'
select * from edtable
go
revert
go

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 give Ed "take ownership" permission on the table

Until Ed has "take ownership" permission, he does not and cannot "own" the table he just created.

There 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.

This 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.

This 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?

Just in case you don't believe it, code below (picks up where other code left off):

-- snip (when I left off, I was Ed)
-- ed cannot get ownership of table
-- this fails
alter authorization on object::fredstuff.edtab to ed
go

-- back to dbo
setuser
go

-- dbo can give the table to ed
-- alter authorization on object::fredstuff.edtab to ed
-- go

-- or dbo can give ed 'take ownership' permission
grant take ownership on fredstuff.edtab to ed
go

setuser 'ed'
go

-- now this works for ed, because he has 'take ownership'
alter authorization on object::fredstuff.edtab to ed
go

-- now ed can SELECT the table
select * from fredstuff.edtab
go

-- ed creates another table in the schema
create table fredstuff.table1 (id int)
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.database_principals
select * from sys.tables
go

-- owned by 'fred' (schema owner)
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed'
select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

setuser 'fred'
go

-- so can fred SELECT both tables
-- because fred is the schema owner
select * from fredstuff.edtab
select * from fredstuff.table1
go

setuser
go

alter authorization on schema::fredstuff to dbo
go

setuser 'fred'
go
-- no access for fred on this table
select * from fredstuff.edtab
-- access for fred on this table
select * from fredstuff.table1
go

setuser
go
setuser 'ed'
go
-- access for ed, he's still the owner
select * from fredstuff.edtab
-- never had access to this table
select * from fredstuff.table1
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.tables
go

select * from sys.database_principals
-- owned by 'dbo' (schema owner), this changed
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed', this did not change

select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

-- snip --

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
go

use test
go

-- 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
go

-- create the schema for fred
create schema fredstuff authorization fred
go

-- 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
go

setuser 'ed'
go

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

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

UI can't stay away from the separation and users and schemas feature. I want to make sure I have it cold, and following up on information I got from Girish Chandler's talk at Win-Dev, I did the following experiement. Posted in the form of a multi-part puzzle. Answer to the first part (with code) tomorrow.

1. You have two logins, Fred and Ed. Neither one has any special privileges
2. You create users for them in a database:
   Fred has a default schema of Fredstuff, which he owns
   Ed has no default schema
3. You grant both of them CREATE TABLE
   And Grant Ed ALTER priviledge on the Fredstuff schema (this lets him CREATE and ALTER objects in the schema).
4. Now Ed issues a CREATE TABLE statement to create a table in the Fredstuff schema

Who owns the table?

More on user-schema separation. In SQL 2000 and previous versions, granting someone CREATE TABLE privilege meant that they could create tables (no surprise there). The tables were "named after them" (e.g. bob.sometable) unless they were DBO. Because of user-schema separation in SQL Server 2005, that's no longer accurate.

I always explain this in terms of what you may not have the ability to do. And that, unless you have a default schema, your default schema is DBO, which you probably can't write to. There's a more polite/positive way of expressing this, courtesy of Dan. To be able to say...create tables, you need:
1. CREATE TABLE permission
2. A schema in which you are allowed to create objects. Or a schema that you or one of your roles own.

BTW, if this doesn't jibe with your experience, you're probably using the GUI (SSMS) or the legacy system stored proc sp_adduser, which creates a schema for you (for backward compatibility) rather than the new, cool (is security cool? yes, I think so), DDL statement CREATE USER. If you're using SSMS, to see what I mean:
1. Go to Security/logins in Object Explorer.
2. Choose to create a new login. Type in a login name/password
3. Click on database access
4. Permit access to a database by checking the Permit checkbox
5. Note that "default schema" and "user" get filled in with your userid
6. Click in the "default schema" cell. Oh.

NOTE that *default schema is a dropdown list*. You get to choose your default schema, or choose not to have one at all, in which case it's DBO.

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:

fred.some_table
dbo.some_table
payroll.some_table

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....