SQL Server 2019 cross-database function call permission

Question

I have come across some interesting behaviour in SQL Server 2019 - it does not seem to occur in earlier versions.

If, in database1, I call a function in the same database, which calls a function in database2, which SELECTS a table in database2, I get "The SELECT permission was denied on the object '{TableName}', database '{DbName}', schema 'dbo'."

If, instead, I call the function in database2 directly (without using a function in database1), the query executes successfully.

My question is: what is the logic behind this? I don't understand why I am allowed to read a table in another database, without the SELECT permission, through a function, but not when I call that function using a function in my current database! Is it due to the function preventing the passing on of permissions? I am assuming at the moment that this is an intended change - but I don't understand the logic behind it.

Below is some code demonstrating the behaviour in a simple way.

/*******************************************
SET UP
*******************************************/
CREATE DATABASE TestDb1
GO
CREATE DATABASE TestDb2
GO

CREATE LOGIN [TestLogin] WITH PASSWORD = '123456a.'
GO

--Create users in each database and add to roles.
USE TestDb1
CREATE USER [TestUser] FOR LOGIN [TestLogin]
CREATE ROLE Db1Role
ALTER ROLE Db1Role ADD MEMBER [TestUser]

USE TestDb2
CREATE USER [TestUser] FOR LOGIN [TestLogin]
CREATE ROLE Db2Role
ALTER ROLE Db2Role ADD MEMBER [TestUser]

--Create table in db1, but do no GRANTs on it.
USE TestDb1
CREATE TABLE dbo._testDb1Table (Col1 INT)
GO

--Create a function in db1, and GRANT EXECUTE.
CREATE FUNCTION dbo._TestDb1Function()
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = (SELECT TOP (1) Col1 FROM dbo._testDb1Table)
    RETURN @Result
END
GO
GRANT EXECUTE ON dbo._TestDb1Function TO Db1Role
GO

--Create a function in db2, and GRANT EXECUTE.
USE TestDb2
GO
CREATE FUNCTION dbo._TestDb2Function()
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = (SELECT TestDb1.dbo._TestDb1Function())
    RETURN @Result
END
GO
GRANT EXECUTE ON dbo._TestDb2Function TO Db2Role
GO

/*******************************************
TESTS
*******************************************/
USE TestDb2

--Querying TestDb1 by calling the TestDb2 function directly works.
EXECUTE AS LOGIN = 'TestLogin'
SELECT TestDb1.dbo._TestDb1Function()
REVERT
GO

--Querying TestDb2 through a scalar function in db2 doesn't work.
--The SELECT permission was denied on the object '_testDb1Table', database 'TestDb1', schema 'dbo'.
EXECUTE AS LOGIN = 'TestLogin'
SELECT dbo._TestDb2Function()
REVERT
GO

/*******************************************
TIDY UP
*******************************************/
USE [master]
DROP LOGIN [TestLogin]
DROP DATABASE TestDb1
DROP DATABASE TestDb2

Answer 1

As per helpful comments by GSerg and Larnu, this behaviour appears to be caused by the scalar UDF inlining feature, added in SQL Server 2019.

It can be fixed by disabling scalar UDF inlining at the database level, in the function definition, or using a query hint.

Edit: as per the answer by Razvan Socol, this has been fixed in SQL Sever 2019 CU9.

Here is the same code as given in the original question, but with these 3 possible solutions inserted into the appropriate places (commented out). Uncommenting any of these 3 solutions allows the script to run without error in SQL Server 2019.

/*******************************************
SET UP
*******************************************/
CREATE DATABASE TestDb1
CREATE DATABASE TestDb2
GO
--SOLUTION 1: Turn off scalar UDF inlining at the database level.
--USE TestDb2
--ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
GO

CREATE LOGIN [TestLogin] WITH PASSWORD = '123456a.'
GO

--Create users in each database and add to roles.
USE TestDb1
CREATE USER [TestUser] FOR LOGIN [TestLogin]
CREATE ROLE Db1Role
ALTER ROLE Db1Role ADD MEMBER [TestUser]

USE TestDb2
CREATE USER [TestUser] FOR LOGIN [TestLogin]
CREATE ROLE Db2Role
ALTER ROLE Db2Role ADD MEMBER [TestUser]

--Create table in db1, but do no GRANTs on it.
USE TestDb1
CREATE TABLE dbo._testDb1Table (Col1 INT)
GO

--Create a function in db1, and GRANT EXECUTE.
CREATE FUNCTION dbo._TestDb1Function()
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = (SELECT TOP (1) Col1 FROM dbo._testDb1Table)
    RETURN @Result
END
GO
GRANT EXECUTE ON dbo._TestDb1Function TO Db1Role
GO

--Create a function in db2, and GRANT EXECUTE.
USE TestDb2
GO
CREATE FUNCTION dbo._TestDb2Function()
RETURNS INT
--SOLUTION 2: Turn off scalar UDF inlining for the function.
--WITH INLINE = OFF
AS
BEGIN
    DECLARE @Result INT = (SELECT TestDb1.dbo._TestDb1Function())
    RETURN @Result
END
GO
GRANT EXECUTE ON dbo._TestDb2Function TO Db2Role
GO

/*******************************************
TESTS
*******************************************/
USE TestDb2

--Querying TestDb1 by calling the TestDb2 function directly works.
EXECUTE AS LOGIN = 'TestLogin'
SELECT TestDb1.dbo._TestDb1Function()
REVERT
GO

--Querying TestDb2 through a scalar function in db2 doesn't work.
--The SELECT permission was denied on the object '_testDb1Table', database 'TestDb1', schema 'dbo'.
EXECUTE AS LOGIN = 'TestLogin'
SELECT dbo._TestDb2Function()
--SOLUTION 3: Turn off scalar UDF inlining for the query which calls the function.
--OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')); --Added line
REVERT
GO

/*******************************************
TIDY UP
*******************************************/
USE [master]
DROP LOGIN [TestLogin]
DROP DATABASE TestDb1
DROP DATABASE TestDb2

Answer 2

This was a bug in SQL Server 2019, caused by scalar UDF inlining. It was fixed in SQL Server 2019 CU9 (published in February 2021). For more details see KB4538581.