Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.
.gif "Topic link icon")
Transact-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. |
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.
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
Reference
IS_SRVROLEMEMBER (Transact-SQL)
Security Catalog Views (Transact-SQL)
sp_addrolemember (Transact-SQL)
sp_addsrvrolemember (Transact-SQL)
sp_droprolemember (Transact-SQL)
sp_dropsrvrolemember (Transact-SQL)