I'm having a problem with name resolution within a stored procedure. I have the following sample code:
/*User Abrin2 has default schema of Abrin2
Assume Abrin2 user executes procedure below
alter proc dbo.test_p --Note the dbo schema
EXECUTE AS 'Abrin2'
create table tempo --Table gets created as Abrin2.tempo
insert into tempo --This line throws an error, because it can't find Abrin2.tempo
select 5 --Why does this not resolve to Abrin2's default schema? Seems to be bug in SQL 2005
/*The above procedure does execute OK if I move the procedure into the Abrin2 schema, (Abrin2.test_p), however, my thinking is this should not be necessary since SQL should look in Abrin2's default schema when Abrin2 is executing the proc. It seems that for the insert into statement (as well as delete), SQL looks only in the same schema the proc is in, then in the dbo schema and fails to look into the caller's default schema. Is this a bug in SQL 2005 engine or am I missing something?
It also executes OK within the Abrin2 schema if I put the INSERT statement in dynamic SQL:
declare @SQL nvarchar(4000)
set @SQL = 'INSERT INTO Tempo
exec sp_executesql @SQL --SQL does correctly look in Abrin2's default Schema (Abrin2)