Select permission denied when using linked server in a function unless inlining is off

We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production environment running SQL 2014.

The statement is:

select function(1234567)

The error message is:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘function’, database ‘database’, schema ‘dbo’.

The function is:

CREATE FUNCTION [dbo].[function](@var1 INT)
RETURNS BIT
BEGIN
    DECLARE @var2 BIT, @var3 BIGINT, var4 INT
    SET @var2 = 0

    SELECT @var3= ISNULL(tbl2.field1,tbl3.field1) FROM tbl1 
    LEFT OUTER JOIN tbl2 WITH (NOLOCK) ON tbl2.field2 = tbl1.field2 
    LEFT OUTER JOIN tbl3 WITH (NOLOCK) ON tbl3.field2 = tbl1.field2
    WHERE tbl1.field2 = @var1

    SELECT @var4 = ISNULL(field4,1) FROM linked_server.database.dbo.tbl4 WHERE field5=Convert(VARCHAR(20),@var3)
    IF(@var4 <> 1)
    BEGIN
        SET @var1 = 1
    END

    RETURN @var1
END

GO

Successful workarounds:

• Individual users can execute the statement inside the function without error
• Modifying the function to include the clause “with inline = off”
• Adding the user executing the function to the sysadmin server role

I’m not sure if we’ve found a bug or if it’s just a case of missing permissions (or something else entirely). We would like to avoid changing the code if possible; elevating user permissions to sysadmin is a non-starter.

We are running CU2. We have not tested on CU3.

UDF inlining is now blocked when the definition references remote tables.

This restriction was added in Microsoft SQL Server 2019 CU6.

If inlining is being attempted by the Optimizer, but failing owing to the use of remote tables (i.e. linked server), then the function can be made to avoid being inlined using WITH INLINE = OFF. You can verify if a function is eligible for inlining by querying column [is_inlineable] in [sys].[sql_modules].

https://sqlperformance.com/2019/01/sql-performance/scalar-udf-sql-server-2019