BUG: Changes to the Windows NT Group Membership Are Not Reflected Immediately in the SQL Server IS_MEMBER Function (812774)

MORE INFORMATION

When you start a process on a Windows NT-based computer, the process acquires your security attributes.
These attributes are stored in the access token for the process. The security attributes include account user rights that control access to system services. The GetTokenInformation function lists your user rights as a user.

On a Windows NT operating system, the security cache is built when you log on. The IS_MEMBER function
queries this GetTokenInformation function to check whether the user belongs to the Windows NT group or not and to determine the user rights.

When you log on to the Windows NT system and start a process, any modifications to the Windows NT group are not updated in the access token information of that process. Therefore, when the SQL Server IS_MEMBER function is executed from the same client, the modifications to the Windows NT group are not reflected.

After a modification to the Windows NT group, if you log out and then log back on to the Windows NT-based computer, the security cache is built with the latest information about the Windows NT group. Therefore, the SQL Server IS_MEMBER function returns the updated information about the Windows NT group.

Steps to Reproduce the Behavior

To reproduce the behavior, follow these steps:

1. Log on to the Windows NT or Windows 2000 operating system based computer by using the <Domain name>\Login1 login that is a member of the <Domain name>\Group1 group that has permission to access SQL Server.
2. Open Query Analyzer, and then connect to SQL Server by using Windows Authentication.
3. Run the following Transact-SQL query in Query Analyzer:

   if is_member('<Domain name>\Group1') = 1
          Print 'The user is member of Group1  group.'
   Else
          Print 'The user is not a member of Group1  group.'
   

   You receive the following error message in the results pane of Query Analyzer:
   The user is member of Group1 group.
   Note: Do not disconnect the Query Analyzer session.
4. Remove the Windows NT login Login1 from the <Domain name>\Group1 group.
5. Run the Transact-SQL query as specified in step 3 by using the same Query Analyzer session. You receive the following error message in the results pane of Query Analyzer:
   The user is member of Group1 group.
   The output of the query remains same although Login1 is removed from the <Domain name>\Group1 group.
   
6. Close Query Analyzer.
7. Open a new connection to SQL Server by using Query Analyzer.
8. Run the Transact-SQL query as specified in step 3. You receive the following error message in the results pane of Query Analyzer.
   The user is member of Group1 group.
   The output of the query remains the same although the new connection is established to SQL Server.
9. Close Query Analyzer, and then log off the computer.
10. Log on to the computer by using the same login as in step 1.
11. Open Query Analyzer, and then connect to SQL Server by using Windows authentication.
12. Run the Transact-SQL query as specified in step 3. You receive the following error message in the results pane of Query Analyzer:
    The user is not a member of Group1 group.
    The security cache is updated when you log on to the Windows NT computer.

Note: The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, places, or events is intended or should be inferred.