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 Feb 4 '15 at 11:05

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.

| improve this answer | |
  • 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]; – youcantryreachingme Jul 4 '19 at 3:23

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

| improve this answer | |
  • 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 Jan 3 '18 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

| improve this answer | |
  • 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 Jan 19 '17 at 15:23
  • 3
    It was probably not a table-valued function, that's why it worked. – Diego Mar 10 '17 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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