Use of IS_MEMBER function with multiple databases

Question

Use of IS_MEMBER function with multiple databases

Luciano Pinto 1

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

Bert Zhou-msft 3,521 Reputation points

2022-04-07T02:42:36.213+00:00

Hi,@Luciano Pinto
You'd better show us your test code and pictures, otherwise we won't be able to test the results out of thin air.

Regards,
Bert Zhou

4 answers

Your answer

Bert Zhou-msft 3,521 Reputation points

2022-04-07T02:42:36.213+00:00

Hi,@Luciano Pinto
You'd better show us your test code and pictures, otherwise we won't be able to test the results out of thin air.

Regards,
Bert Zhou

Answer 1

Erland Sommarskog 137.7K MVP Volunteer Moderator

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

Answer 2

Luciano Pinto 1

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?

Tom Phillips 17,711 Reputation points

2022-04-07T14:08:55.44+00:00

In the future, please post actual code, not screen prints.

I was able to duplicate your results in SQL 2019. I do not currently have SQL 2016 installed, so I cannot test it.
Luciano Pinto 1 Reputation point

2022-04-07T14:15:05.813+00:00

"In the future, please post actual code, not screen prints." I will thanks.

The problem is that i am upgrading from 2016 to 2019 and i have a large amount of dependencies (hundreds) for a custom IsMember function... and i cant find anything in the release logs , documentation etc saying that this behavior was changed; in fact every document says "NO BREAKING CHANGES", and as far as i can tell this is a breaking change.
Unless there's a configuration that i am missing that enables this behavior.
Tom Phillips 17,711 Reputation points

2022-04-07T16:22:29.98+00:00

If this is a blocking issue, you should contact MS Support directly for a quicker resolution.
Luciano Pinto 1 Reputation point

2022-04-07T16:35:06.947+00:00

ill probably have too, this is an ongoing upgrade, still have a lot of time :) maybe some one around will shed some light on the subject

Answer 3

Naomi Nosonovsky 8,906

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

Luciano Pinto 1 Reputation point

2022-04-07T14:00:35.87+00:00

In the 3rd Image in the select statement i am using SYSTEM_USER to show the logged use, so i am using the same user in all the selects, both in SQL 2016 and SQL 2019

Both database are exactly the same (same dump)

"Your function checks rights of the currently logged user.", dont reealy know what u mean here..

Best Regards.
Naomi Nosonovsky 8,906 Reputation points

2022-04-07T18:08:04.117+00:00

You answered my question anyway (I was thinking the function may receive both user and role). Yes, seems like a change to me.

Answer 4

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

1 deleted comment

Comments have been turned off. Learn more