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

Question

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.

• Paul White: Microsoft have confirmed this issue and are working on a possible fix for a future CU.

Answer 1

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

This restriction was added in Microsoft SQL Server 2019 CU6.

Answer 2

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