Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Indicates whether a specified database principle is a member of the specified database role.
Applies to: SQL Server (SQL Server 2012 through current version.
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 is not 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 does not respond, IS_ROLEMEMBER returns role membership information by accounting for the user and its local groups only. If the user specified is not 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 database principal that is being queried must be present in sys.database_principals, or IS_ROLEMEMBER will return NULL. This indicates that the database_principal is not valid in this database.
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 is not available, the call to IS_ROLEMEMBER will return accurate information when the Windows principle 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 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 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.';