31

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!

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
Jack Frost
  • 461
  • 1
  • 5
  • 8
  • As you are now returning a table, the GRANT statement needs to be GRANT SELECT, rather than GRANT EXECUTE. – Mike Feb 04 '15 at 11:05

3 Answers3

45

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.

Mark Sinkinson
  • 10,547
  • 3
  • 46
  • 54
  • 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 04 '19 at 03:23
1

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

RDFozz
  • 11,631
  • 4
  • 24
  • 38
jason
  • 21
  • 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 03 '18 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

Ahmed Negm
  • 111
  • 3