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