Sql-server – Why scalar valued functions need execute permission rather than select

functionspermissionssql servert-sql

I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select?

meanwhile a table valued functions works just fine with only select permission or db_datareader membership.

to be more clear here is my example:
I need a user that has read only permission to the database.
so I created a user called testUser and give it db_datareader membership.
then I created a table valued function called fn_InlineTable. And all is great. testUser runs this SQL all day long

select * from dbo.fn_InlineTable

then I need a scalar function , so I created a scalar function called fn_ScalarTest.
testUser cannot run this SQL

Select dbo.fn_ScalarTest(1)

Well understandably: it is because I have not given "testUser" permission to execute fn_ScalarTest.

My question is: based on this link https://stackoverflow.com/questions/6150888/insert-update-delete-with-function-in-sql-server,
that says a FUNCTION cannot be used to perform actions that modify the database state. So why not let a scalar function to be used with the same "SELECT" permission rather than execute permission??

I hope my question makes sense. Thank you.

Best Answer

Most likely the primary reason is that Table-Valued Functions return a Result Set, just like Tables and Views. This means that they can be used in the FROM clause (including JOINs and APPLYs, etc) of SELECT, UPDATE, and DELETE queries. You cannot, however, use a Scalar UDF in any of those contexts.

Secondarily, you can also EXECUTE a Scalar UDF. This syntax is quite handy when you have default values specified for input parameters. Take the following UDF, for example:

CREATE FUNCTION dbo.OptionalParameterTest (@Param1 INT = 1, @Param2 INT = 2)
RETURNS INT
AS
BEGIN
    RETURN @Param1 + @Param2;
END;

If you want to treat any of the input parameters as "optional", you still need to pass in the DEFAULT keyword when calling it like a function since the signature is fixed:

DECLARE @Bob1 INT;

SET @Bob1 = dbo.OptionalParameterTest(100, DEFAULT);

SELECT @Bob1;
-- Returns: 102

On the other hand, if you EXECUTE the function, then you can treat any parameters with a default value as truly optional, just like you can with Stored Procedures. You can pass in the first n parameters without specifying parameter names:

DECLARE @Bob2 INT;

EXEC @Bob2 = dbo.OptionalParameterTest 50;

SELECT @Bob2;
-- Returns: 52

You can even skip the first parameter by specifying parameter names, again, just like with Stored Procedures:

DECLARE @Bob3 INT;

EXEC @Bob3 = dbo.OptionalParameterTest @Param2 = 50;

SELECT @Bob3;
-- Returns: 51

UPDATE

Why might you want to use the EXEC syntax to call a scalar UDF just like a Stored Procedure? Occasionally there are UDFs that are great to have as UDFs since they can be added to a query and operate over the set of rows returned, whereas if the code were in a Stored Procedure then it would need to be placed into a cursor in order to iterate over a set of rows. But then there are times that you want to call that function on a single value, possibly from within another UDF. Calling a UDF for a single value can be done as either:

SELECT dbo.UDF('some value');

in which case you get a return value in a result set (a result set won't work). Or it could be done as follows:

DECLARE @Dummy INT;

SET @Dummy = dbo.UDF('some value');

in which case you need to declare the @Dummy variable;

HOWEVER, with the EXEC syntax, you can avoid both of those annoyances:

EXEC dbo.UDF 'some value';

ALSO, scalar UDFs have their executions plans cached. This means that it is possible to run into parameter sniffing issues if there are queries in the UDF that have execution plans. For scenarios where it is feasible to use the EXEC syntax, then it is possible to also use the WITH RECOMPILE option to ignore the plans compiled value for that execution. For example:

SETUP:

GO
CREATE FUNCTION dbo.TestUDF (@Something INT)
RETURNS INT
AS 
BEGIN
   DECLARE @Ret INT;
   SELECT @Ret = COUNT(*)
   FROM   sys.indexes si
   WHERE  si.[index_id] = @Something;

   RETURN @Ret;
END;
GO

TEST:

DECLARE @Val INT;

SET @Val = dbo.TestUDF(1);
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 -- uses compiled value of (1)
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 WITH RECOMPILE; -- uses compiled value of (0)
SELECT @Val;

EXEC @Val = dbo.TestUDF 3 -- uses compiled value of (1)
SELECT @Val;
Related Question