May 19, 2016 at 3:11 pm
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.
May 19, 2016 at 4:26 pm
Okay, apparently if I change databases and run any query against the new DB, then switch back and run IS_ROLEMEMBER it provides the "refresh" I need, but I would prefer a more elegant solution if anyone knows of one.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply