Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
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: Tuesday, January 5, 2016 7:59 AM
Points: 5, Visits: 143
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
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



Group: General Forum Members
Last Login: Today @ 5:44 AM
Points: 10,807, Visits: 14,830
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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