Am I doing it right...?

I have a function that returns money...

CREATE FUNCTION functionName( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )  RETURNS money AS BEGIN

  DECLARE @v_dint money   set @v_dint = computation_here
     set @v_dint = round(@v_dint, 2)

  RETURN @v_dint    
Grant execute on functionName to another_user 

Im just wondering if this is possible to be converted to iTVF?

I've tried doing this but I got an error:

CREATE FUNCTION functionName ( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )  
RETURN SELECT returnMoney = computation_here  
Grant execute on functionName to another_user  Go


Msg 4606, Level 16, State 1, Line 2 Granted or revoked privilege EXECUTE is not compatible with object.

This function is used like this:

update table_name set interest = functionName(col1,col2...) where...

Thanks in advance!

  • As you are now returning a table, the GRANT statement needs to be GRANT SELECT, rather than GRANT EXECUTE.
    – Mike
    Commented Feb 4, 2015 at 11:05

3 Answers 3


Scalar functions require EXECUTE permissions, however when you've converted to a Table Valued Function the permissions required change to SELECT.

You must now GRANT SELECT ON functionName TO another_user;

From BOL:

Users other than the owner must be granted EXECUTE permission on a function (if the function is scalar-valued) before they can use it in a Transact-SQL statement. If the function is table-valued, the user must have SELECT permissions on the function before referencing it.

  • And per RDFozz's comment on another answer, if the user name has special characters (like a backslash, as in DOMAINNAME\username) then you need to put the username in square brackets, viz: grant select on functionName to [DOMAINNAME\username]; Commented Jul 4, 2019 at 3:23

It needs to be GRANT SELECT ON functionName TO [another_user] - with brackets.

  • The brackets are optional unless the username includes a special character. Since backslash is a special character, and is often going to be part of the user name for users w/ Windows authentication logins, it's probably safest to be in the habit of using the brackets.
    – RDFozz
    Commented Jan 3, 2018 at 21:08

I tried to use:

GRANT SELECT ON functionName TO another_user

But it didn't work, then, I used EXECUTE instead of SELECT, and it works now

  • I'm curious as to how you managed this, as attempting to grant execute on a SQL function will always throw an error.
    – Ian Kemp
    Commented Jan 19, 2017 at 15:23
  • 4
    It was probably not a table-valued function, that's why it worked.
    – Diego
    Commented Mar 10, 2017 at 2:00
  • The was helpful, mine was an aggregate function
    – luiscla27
    Commented Jun 3, 2022 at 1:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.