20

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!

  • 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
32

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]; – youcantryreachingme Jul 4 at 3:23
0

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 Jan 3 '18 at 21:08
-4

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 Jan 19 '17 at 15:23
  • 2
    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.