Redigera

Dela via

IS_MEMBER (Transact-SQL)

  • Artikel

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

The IS_MEMBER function is supported for Microsoft Entra groups. The one case where IS_MEMBER doesn't work is if the group is the Microsoft Entra administrator for the SQL instance.

Transact-SQL syntax conventions

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Syntax

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

Arguments

' group '
Applies to: SQL Server 2008 (10.0.x) and later

Is the name of the Windows or Microsoft Entra group that is being checked. A Windows group 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 isn't a member of group or role.
1 Current user is a member of group or role.
NULL Either group or role isn't 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 doesn't reflect changes in group membership that are made after a user connects to an instance of SQL Server. Windows group membership can't be queried by a SQL Server login or a SQL Server application role.

To add and remove members from a database role, use ALTER ROLE (Transact-SQL). To add and remove members from a server role, use ALTER SERVER ROLE (Transact-SQL).

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 isn't a member of the role, this function correctly reports that the user isn't a member of the db_owner role, even though the user has the same permissions.

Members of the sysadmin fixed server role enter every database as the dbo user. Checking permission for member of the sysadmin fixed server role, checks permissions for dbo, not the original login. Since dbo can't be added to a database role and doesn't exist in Windows groups, dbo always returns 0 (or NULL if the role doesn't exist).

To determine whether another SQL Server login is a member of a database role, use IS_ROLEMEMBER (Transact-SQL). To determine whether a SQL Server login is a member of a server role, use IS_SRVROLEMEMBER (Transact-SQL).

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

See Also

IS_SRVROLEMEMBER (Transact-SQL)
Principals (Database Engine)
Security Catalog Views (Transact-SQL)
Security Functions (Transact-SQL)