SQL Server – How to Grant Permissions on a Table-Valued Function

functionssql server

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!

Best Answer

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.

Related Question