Share via

  • Article

IS_MEMBER (Transact-SQL)

Indicates whether the current user is a member of the specified Microsoft Windows group or SQL Server database role.

Topic link iconTransact-SQL Syntax Conventions

Syntax

IS_MEMBER ( { 'group' | 'role' } )

Arguments

  • 'group'
    Is the name of the Windows group that is being checked; must be in the format Domain\Group. group is sysname.

  • 'role'
    Is the name of the SQL Server role that is being checked. role is sysname and can include the database fixed roles or user-defined roles, but not server roles.

Return Types

int

Remarks

IS_MEMBER returns the following values.

Return value

Description

0

Current user is not a member of group or role.

1

Current user is a member of group or role.

NULL

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.

Examples

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