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:
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
- 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