This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 45%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELP%3C/text%3E%3C/svg%3E)
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
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(281.6, 56.00000000000001%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBZ%3C/text%3E%3C/svg%3E)
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
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 commentComments have been turned off. Learn more
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Are you logged as this user in both SQL Server instances? Your function checks rights of the currently logged user.
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.
You answered my question anyway (I was thinking the function may receive both user and role). Yes, seems like a change to me.
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?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
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.
"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.
If this is a blocking issue, you should contact MS Support directly for a quicker resolution.
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
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.