Hi all. I'm trying to drop users from roles via T-SQL, then check to ensure the user has been dropped. The drop happens but the immediate check using IS_ROLEMEMBER fails, indicating the user is still in the role. I suspect there needs to be a "refresh" of some kind between the calls, but I'm not certain how to do that. I have tried placing a USE SomeOtherDb; call between them, hoping that changing context would do it, but no such luck.
The call to IS_ROLEMEMBER works fine once execution is stopped and the call is run separately. It seems to fail only when called immediately after ALTER ROLE. The parameters contain the correct data.
EXEC('ALTER ROLE [' + @DBRole + '] DROP MEMBER [' + @userid + '];');
IF ((IS_ROLEMEMBER (@DBRole, @userid)) = 0) ...
Has anyone seen this behavior before?
Thanks. Kurt.