BUG: User's Role Not Updated Instantly in Multiple Sessions (207718)

The information in this article applies to:

Microsoft SQL Server 7.0
Microsoft SQL Server 2000 (all editions)

This article was previously published under Q207718

BUG #: 52102 (SQLBUG_70)

SYMPTOMS

When adding a user to a fixed role, such as db_owner or db_securityadmin, the user's role is not updated immediately in other sessions if the user is logged in with multiple sessions. This behavior is not seen in the case of user-defined roles. In that case, the user's role is updated immediately in all the sessions that the user is logged in.

CAUSE

The security cache is not updated with fixed role membership bits until the next time the database is used (internally) or the cache is rebuilt for some other reason (for example, checking select permissions).

WORKAROUND

To work around this problem, force the security cache to update the fixed role membership bits by executing statements like the following:

use <db_name>

-or-

select is_member ('<fixed role>')

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Modification Type:	Major	Last Reviewed:	10/17/2003
Keywords:	kbBug kbpending KB207718