IS_ROLEMEMBER (Transact-SQL)

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

Indicates whether a specified database principal is a member of the specified database role.

Transact-SQL syntax conventions

Note

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

Syntax

IS_ROLEMEMBER ( 'role' [ , 'database_principal' ] )  

Arguments

' role '
Is the name of the database role that is being checked. role is sysname.

' database_principal '
Is the name of the database user, database role, or application role to check. database_principal is sysname, with a default of NULL. If no value is specified, the result is based on the current execution context. If the parameter contains the word NULL, it will return NULL.

Return Types

int

Return value	Description
0	database_principal isn't a member of role.
1	database_principal is a member of role.
NULL	database_principal or role isn't valid, or you don't have permission to view the role membership.

Remarks

The IS_ROLEMEMBER function isn't supported for a Microsoft Entra administrator when the administrator is a member of a Microsoft Entra group. The IS_ROLEMEMBER function is supported for Microsoft Entra users that are members of a Microsoft Entra group, unless that group is the Microsoft Entra admin.

Use IS_ROLEMEMBER to determine whether the current user can perform an action that requires the database role's permissions.

If database_principal is based on a Windows login, such as Contoso\Mary5, IS_ROLEMEMBER returns NULL, unless the database_principal has been granted or denied direct access to SQL Server.

If the optional database_principal parameter isn't provided and if the database_principal is based on a Windows domain login, it may be a member of a database role through membership in a Windows group. To resolve such indirect memberships, IS_ROLEMEMBER requests Windows group membership information from the domain controller. If the domain controller is inaccessible or doesn't respond, IS_ROLEMEMBER returns role membership information by accounting for the user and its local groups only. If the user specified isn't the current user, the value returned by IS_ROLEMEMBER might differ from the authenticator's (such as Active Directory) last data update to SQL Server.

If the optional database_principal parameter is provided, the user must exist in sys.database_principals, or IS_ROLEMEMBER returns NULL.

When the database_principal parameter is a based on a domain login or based on a Windows group and the domain controller is inaccessible, calls to IS_ROLEMEMBER will fail and might return incorrect or incomplete data.

If the domain controller isn't available, the call to IS_ROLEMEMBER returns accurate information when the Windows principal can be authenticated locally, such as a local Windows account or a SQL Server login.

IS_ROLEMEMBER always returns 0 when a Windows group is used as the database principal argument, and this Windows group is a member of another Windows group which is, in turn, a member of the specified database role.

The User Account Control (UAC) found in Windows Vista and Windows Server 2008 might also return different results. This would depend on whether the user accessed the server as a Windows group member or as a specific SQL Server user.

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 will correctly report 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).

Related Functions

To determine whether the current user is a member of the specified Windows group, Microsoft Entra group, or SQL Server database role, use IS_MEMBER (Transact-SQL). To determine whether a SQL Server login is a member of a server role, use IS_SRVROLEMEMBER (Transact-SQL).

Permissions

Requires VIEW DEFINITION permission on the database role.

Examples

The following example indicates whether the current user is a member of the db_datareader fixed database role.

IF IS_ROLEMEMBER ('db_datareader') = 1  
   print 'Current user is a member of the db_datareader role'  
ELSE IF IS_ROLEMEMBER ('db_datareader') = 0  
   print 'Current user is NOT a member of the db_datareader role'  
ELSE IF IS_ROLEMEMBER ('db_datareader') IS NULL  
   print 'ERROR: The database role specified is not valid.';  

See Also

CREATE ROLE (Transact-SQL)
ALTER ROLE (Transact-SQL)
DROP ROLE (Transact-SQL)
CREATE SERVER ROLE (Transact-SQL)
ALTER SERVER ROLE (Transact-SQL)
DROP SERVER ROLE (Transact-SQL)
IS_MEMBER (Transact-SQL)
IS_SRVROLEMEMBER (Transact-SQL)
Security Functions (Transact-SQL)