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.
