grant permission problem on functions that returns table data type

problem on grant permission to user
Was this post helpful ?
I have two kinds of functions in the database that need to grant exec
permission to user appUser.
The first kind of function return ordinary datatype, let's call it
funcReturnDataType here.
The second kind of function return table datatype, let's call it
funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser
However when I issued:
grant exec on funcReturnTable to appUser
I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Any suggestions to resolve this problem?
Thank you!
The following is an example of a function that returns a table data type:
CREATE FUNCTION funcReturnTable
(
@.i int
)
RETURNS TABLE
AS
RETURN
(
Select Distinct FoodType from Food where region=@.i
)
For a table-valued function, specify SELECT instead of EXEC.
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:3C2893C3-7394-40CE-A098-57C06ED96C57@.microsoft.com...
> problem on grant permission to user
> Was this post helpful ?
>
> I have two kinds of functions in the database that need to grant exec
> permission to user appUser.
> The first kind of function return ordinary datatype, let's call it
> funcReturnDataType here.
> The second kind of function return table datatype, let's call it
> funcReturnTable
> When I issued the folloing command to appUser, no problem.
> grant exec on funcReturnDataType to appUser
> However when I issued:
> grant exec on funcReturnTable to appUser
> I got the following error message:
> Server: Msg 4606, Level 16, State 1, Line 1
> Granted or revoked privilege EXECUTE is not compatible with object.
> Any suggestions to resolve this problem?
> Thank you!
> --
> The following is an example of a function that returns a table data type:
> CREATE FUNCTION funcReturnTable
> (
> @.i int
> )
> RETURNS TABLE
> AS
> RETURN
> (
> Select Distinct FoodType from Food where region=@.i
> )
>
>
>