How can i use default schemas of users in a function ?

Hi,

I've a Problem with a Function called by different users.

I want the different callers to query a table in their own default Schema.

So if i refence the full Path [Schema].[Tablename] it works Properly.

If i just Use the [Tablename] for reference it fails.

The function was created with EXECUTE AS CALLER option ;

if I call it with dynamic SQL i retreive the error-message

"Only functions and extended stored procedures can be executed from within a function."

does anybody know a solution for this problem?

thanks in advance

Raimund

Raimund wrote:

So if i refence the full Path [Schema].[Tablename] it works Properly.

If i just Use the [Tablename] for reference it fails.

The function was created with EXECUTE AS CALLER option ;

I quote from Books Online (search for "Object Visibility and Qualification Rules")

"

USE DBY SELECT * FROM DBY..TableX

Because LoginX is associated with UserA in DBY, SQL Server first looks for DBY.UserA.TableX. If there is no table with this name, SQL Server looks for a table DBY.dbo.TableX. "

So engine look for [yourCallerUser].[Tablename] and for [dbo].[Tablename] and find no [Tablename]

Then I think you have to maintain full path [Schema].[Tablename] to objects reference.

|||

Hm,

so i guess i have to path the query-results by parameter to the function.

It looks horrible but it works.

Best Regards

Raimund