newbe question: calling function inside select

Hi!

I have a scalar function that returns integer:
xview (int)

Now, I'm trying to build a procedure that has the following select
inside:

select atr1, xview(atr2)
from tablename

But, I get the 'Invalid name' error when I try to execute that
procedure.

If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.

I'm a bit confused, so any hint is very welcomed.

Thanks!

Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.

But those are the rules. You must refer to a scalar function with a
two-part name.

I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?

Ok, let's put is this way.
I have 'xview' function.

I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?

It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.

Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.

USER will return userX.

The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.

The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.

Aha. That is very good. Thank you for the information!

Mario.