Something to watch out for when using IS_MEMBER() in TSQL

I recently worked on an interesting issue with my good friend Igor (@sqlsantos), where we were facing performance issues with a piece of code that used the IS_MEMBER () function. Basically, the IS_MEMBER function is used to find out whether the current user (windows/sql login used for the current session) is a member of the specified Windows group or SQL server database role.

In the specified code, the IS_MEMBER function was being used to determine the windows group membership of the windows login. The windows groups were segregated according to geographical areas, and based on the user's group membership, the result set was filtered to show rows for only those geographical areas for which the user was a member of the corresponding groups in Active Directory.

Here's an example of a piece of code where we perform this check:

With SalesOrgCTE AS(


  MinSalesOrg, MaxSalesOrg


  RAuthorized WITH (NOLOCK)


  IS_MEMBER([Group]) = 1


The problem was that the complete procedure where we were using IS_MEMBER was taking several minutes to complete, for a table where the max result set cardinality was in the range of 18000-20000. We noticed the following wait types while the procedure was executing:



I did some research on these waits, and found that since both of these are related to the communication/validation from Active Directory, they lie outside of SQL server, and there's no changes we can do from a configuration standpoint to help reduce/eliminate these waits.

Next, we studied the code, broke it down and tested the performance of the various sections that used the IS_MEMBER function, and found that the main section responsible for the execution time was the "WHERE" condition where we were using the result set of the code mentioned above. This is what the "WHERE" clause looked like:






  SORGNBR BETWEEN MinSalesOrg AND MaxSalesOrg) > 0

Notice that in this code, we've asked SQL to check the value of SORGNBR for each row, and if it's between MinSalesORG and MaxSalesOrg, then add it to the rowcount. We observed that due to this design, it had to make a trip to AD for validating each row, which meant quite a long time for a 18000-20000 row result set, which was responsible for the slow performance of the procedure.

We did some more research with different approaches for the where clause, and the combined efforts of myself, Igor and his team resulted in the following where clause whose performance was acceptable:




       SELECT MinSalesOrg FROM SalesOrgCTE




       SELECT MaxSalesOrg FROM SalesOrgCTE


If you look carefully, you'll notice that in this code snippet, we'll need to communicate with AD only twice, thereby improving the performance of the procedure as a whole.

Summing up: The importance of writing good code cannot be over-emphasized. It's good coding practices like this that lead to performance gains most of the time.

Hope this helps.

Comments (8)

  1. That indeed is helpful piece of information. Thanks for sharing.

  2. Thanks for appreciating my friend…!!! 🙂

  3. Azad Raosaheb Sale says:

    Clear and Crisp Explanation. Thanks Harshdeep

  4. You're welcome…!!! Thanks for appreciating Azad…!!!

  5. Great post .. Thanks for Sharing Harsh ..

  6. You're very welcome…!!!! Thanks for appreciating Gurpreet…!!!

  7. Ranjit says:

    Great post! I have a question though… MSDN says that the function IS_MEMBER determines Windows group membership by examining an access token that is created by Windows. So, does it actually communicate to AD every time it is called or gets the information from the access token?

  8. Hi Ranjit,

    Thanks for your kind words. And yes, from what I understand, the is_member function communicates with AD each time it is called.



Skip to main content