28

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    
END 
GO 
Grant execute on functionName to another_user 
Go

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 )  
RETURNS TABLE AS 
RETURN SELECT returnMoney = computation_here  
GO  
Grant execute on functionName to another_user  Go

ERROR:

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!

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

3 Answers 3

41

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.

1
  • 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]; Jul 4, 2019 at 3:23
1

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

1
  • 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, 2018 at 21:08
-3

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

3
  • 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, 2017 at 15:23
  • 4
    It was probably not a table-valued function, that's why it worked.
    – Diego
    Mar 10, 2017 at 2:00
  • The was helpful, mine was an aggregate function
    – luiscla27
    Jun 3 at 1:45

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.