October 12, 2007 at 11:17 am
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'
as
create table tempo --Table gets created as Abrin2.tempo
(
[id] bigint
)
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
select 5'
exec sp_executesql @sql --SQL does correctly look in Abrin2's default Schema (Abrin2)
*/
October 19, 2007 at 8:40 am
First I want to state that I am relatively new to Sql 2005 and schemas so I still think in terms of owners, but I think I can give you a good idea as to why you are seeing the behavior.
In the sp the sp is going to look for objects with the same owner as the sp, unless you qualify the name with the schema/owner. But when creating objects it will use the UserName unless the user is a dbo and then will create the object in dbo. So your sp is looking for dbo.tempo and not finding it. Similarly if you have Abrin3.Table referenced in a dbo.sp and the user does not have rights to the Abrin3 schema or direct rights on Abrin3.table then the sp will fail.
It is working with dynamic sql because dynamic sql executes in a different context then the stored procedure it is executed from, no longer in dbo.
Hope this helps, and that if I am in error someone else clears is up.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply