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 objects2. Look in user's deafult schema3. 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 objects2. Look in *procedure* schema3. 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 = edstuffgo-- defaultcreate schema edstuff authorization edgo-- named after edcreate schema ed authorization edgo-- another schema for procscreate schema edprocs authorization edgogrant create table to edgrant create procedure to edgo
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))goinsert 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 edprocscreate procedure edprocs.getedasselect * from edtablego
-- i'm in edstuffselect * from edtablego
-- i'm in edprocsexecute edprocs.getedgo
drop table edprocs.edtable-- invalid object name 'edtable'execute edprocs.getedgo
drop table edstuff.edtable-- invalid object name 'edtable'select * from edtablegorevertgo
Theme design by Jelle Druyts
Pick a theme: BlogXP sqlx BlogXP sqlx
Powered by: newtelligence dasBlog 2.0.7226.0
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2008, Bob Beauchemin
E-mail