\nIn 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.\n<\/p>\n
\nWhen SQL Server resolves a one-part object name, the object resolution is slightly different if you're inside a stored procedure.\n<\/p>\n
\nIf batch or dynamic SQL:
\n1. Look in 'sys' schema for system objects
\n2. Look in user's deafult schema
\n3. Look in dbo schema\n<\/p>\n
\nNote 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.\n<\/p>\n
\nIf procedural code:
\n1. Look in 'sys' schema for system objects
\n2. Look in *procedure* schema
\n3. Look in dbo schema\n<\/p>\n
\nNote 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.\n<\/p>\n
\nHere's a code snippet that (hopefully) make this clearer:\n<\/p>\n
\ncreate login ed with password='StrongPW!'
\ncreate user ed for login ed with default_schema = edstuff
\ngo
\n— default
\ncreate schema edstuff authorization ed
\ngo
\n— named after ed
\ncreate schema ed authorization ed
\ngo
\n— another schema for procs
\ncreate schema edprocs authorization ed
\ngo
\ngrant create table to ed
\ngrant create procedure to ed
\ngo\n<\/p>\n
\nexecute as user='ed'
\ncreate table edtable (id int, description varchar(100))
\ncreate table ed.edtable (id int, description varchar(100))
\ncreate table edprocs.edtable (id int, description varchar(100))
\ngo
\ninsert edtable values(1, 'im in edstuff')
\ninsert ed.edtable values(2, 'im in ed')
\ninsert edprocs.edtable values(3, 'im in edprocs')
\ngo
\n— procedure not in default schema, but in edprocs
\ncreate procedure edprocs.geted
\nas
\nselect * from edtable
\ngo\n<\/p>\n
\n— i'm in edstuff
\nselect * from edtable
\ngo\n<\/p>\n
\n— i'm in edprocs
\nexecute edprocs.geted
\ngo\n<\/p>\n
\ndrop table edprocs.edtable
\n— invalid object name 'edtable'
\nexecute edprocs.geted
\ngo\n<\/p>\n
\ndrop table edstuff.edtable
\n— invalid object name 'edtable'
\nselect * from edtable
\ngo
\nrevert
\ngo<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,34],"tags":[],"class_list":["post-922","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-schemas"],"yoast_head":"\n