Name resolution of Default Schemas in Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Name resolution of Default Schemas in Stored...

Name resolution of Default Schemas in Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

Mike Johnson-491593

Posted Friday, October 12, 2007 11:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 10, 2012 12:18 PM
Points: 5, Visits: 121

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

Jack Corbett

Posted Friday, October 19, 2007 8:40 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959

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 »

Permissions