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.