Use of IS_MEMBER function with multiple databases

Question

Hi,

I'm having the following problem

cenario:

1 -StoreProcedure A in database A calls Function B from Database B
2- Function B:
Return IS_MEMBER('ROLE')
3- the current user is a member of 'ROLE' on database B and not a member on Database A

In SQLServer 2019 when i call StoreProcedure A from Database A it returns 'False', but if i call Function B from database B it returns true.

With SQLServer 2016 both return true;

Was there a change 2019? Is it a server configuration? Documentation doesn't mention any changes.

Any help or insights would be much appreciated.

Best Regards

Answer 1

This is a bug which is related to a change to user-defined functions in SQL 2019. Previously they were black boxes, but starting with SQL 2019, SQL Server now attempts to inline user-defined functions. This can have enormous benefit for performance.

However, in this case it leads to incorrect results, because is_member is evaluated against the current database and not the database in the UDF call.

I have submitted a bug report for this here:
https://feedback.azure.com/d365community/idea/655ed5a1-b7b6-ec11-a81c-6045bd7d1bee.

Below is a repro that demonstrates the issue. If you look through the repro, you also find the workaround: add the line WITH INLINE = OFF to the UDF.

Note: As Tom pointed out, this is the kind of repro we want. Images are difficult to copy and paste from.

CREATE LOGIN AnaCarvalho WITH PASSWORD = 'Vital-y-sua-moto'
CREATE DATABASE DB1
go
USE DB1
go
CREATE ROLE RoleA
CREATE USER AnaCarvalho
ALTER ROLE RoleA ADD MEMBER AnaCarvalho
go
CREATE OR ALTER FUNCTION MyIsMember(@GroupRoleName varchar(128))
RETURNS integer 
-- WITH INLINE = OFF
AS
BEGIN 
   RETURN is_member(@GroupRoleName)
END
go
GRANT EXECUTE ON dbo.MyIsMember TO AnaCarvalho
go
CREATE DATABASE DB2
go
USE DB2
go
CREATE USER AnaCarvalho
go
EXECUTE AS LOGIN = 'AnaCarvalho'
go
SELECT DB1.dbo.MyIsMember('RoleA')
go
REVERT
go
USE tempdb
go
DROP DATABASE DB1
DROP DATABASE DB2
DROP LOGIN AnaCarvalho

Answer 2

We would need to see a repro that demonstrates the issue. The repro would have to create the databases, the user and the role.

Most likely when you test it, you will find that the result is the same on SQL 2016 and SQL 2019.

My guess is that the difference you are seeing is due to that permissions are set up differently.

But as I said, provide a repro, if you want us to take a closer look.

Answer 3

Ok, ill try to show the snipets:
as u can see the user 'Anacarvalho' exists in both Test_DBA and Test_DBB however it only has the ROLE 'RoleA' in Test_DBB

consider the folowing function in Test_DBB

when i execute in 2019:

when executed in 2016

any thouts?

Answer 4

Are you logged as this user in both SQL Server instances? Your function checks rights of the currently logged user.