Use of IS_MEMBER function with multiple databases

Luciano Pinto 21 Reputation points
2022-04-06T13:05:48.13+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,584 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-04-07T21:17:51.117+00:00

    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
    
    2 people found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-04-06T21:58:23.177+00:00

    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.

    0 comments No comments

  2. Luciano Pinto 21 Reputation points
    2022-04-07T10:42:10.397+00:00

    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

    190818-image.png

    consider the folowing function in Test_DBB

    190934-image.png

    when i execute in 2019:

    190927-image.png

    when executed in 2016
    190885-image.png

    any thouts?


  3. Naomi 7,366 Reputation points
    2022-04-07T13:51:17.76+00:00

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