Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Name resolution of Default Schemas in Stored Procedures Expand / Collapse
Author
Message
Posted Friday, October 12, 2007 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 24, 2014 10:20 AM
Points: 5, Visits: 127
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)
*/
Post #410139
Posted Friday, October 19, 2007 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:26 AM
Points: 10,381, Visits: 13,436
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #412836
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse