Indicates whether the current user is a member of the specified Microsoft Windows group or SQL Server database role.
IS_MEMBER returns the following values.
Current user is not a member of group or role.
Current user is a member of group or role.
Either group or role is not valid. When queried by a SQL Server login or a login using an application role, returns NULL for a Windows group.
IS_MEMBER determines Windows group membership by examining an access token that is created by Windows. The access token does not reflect changes in group membership that are made after a user connects to an instance of SQL Server. Windows group membership cannot be queried by a SQL Server login or a SQL Server application role.
This function evaluates role membership, not the underlying permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. If the user has the CONTROL DATABASE permission but is not a member of the role, this function will correctly report that the user is not a member of the db_owner role, even though the user has the same permissions.
The following example checks whether the current user is a member of a database role or a Windows domain group.
-- Test membership in db_owner and print appropriate message. IF IS_MEMBER ('db_owner') = 1 PRINT 'Current user is a member of the db_owner role' ELSE IF IS_MEMBER ('db_owner') = 0 PRINT 'Current user is NOT a member of the db_owner role' ELSE IF IS_MEMBER ('db_owner') IS NULL PRINT 'ERROR: Invalid group / role specified' GO -- Execute SELECT if user is a member of ADVWORKS\Shipping. IF IS_MEMBER ('ADVWORKS\Shipping') = 1 SELECT 'User ' + USER + ' is a member of ADVWORKS\Shipping.' GO