ryan.mcatee (3/12/2013)
To clarify, say we have three tables:schemaA.MyTable
schemaB.MyTable
dbo.MyTable
If my current-logged in user's default schema is schemaA and the default schema for the database is schemaB. And we create a stored procedure named dbo.SomeStoredProcedure that does this:
SELECT * FROM MyTable
It will actually query from dbo.MyTable, not schemaA.MyTable or schemaB.MyTable.
If someone could explain this behavior, I would appreciate it.
for objects not qualified with the schema name,SQL Server looks for the object in the default schema your user belongs to first, then in dbo schema. if my default schema is SchemaC, SchemaA and SchemaB are not even checked to see if the object exists... only SchemaC then
In your procedure, you would simply see that behaviour being applied: objects not qualified with the schema name will assume dbo, which is the schema of the containing object and if it doesn't exist, would fail with object not found.
edit: corrected because my assumptions were way way wrong! Thanks Lynn Pettis!
Lowell