IS_MEMBER (Transact-SQL)
SQL Server 2005
Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.
Transact-SQL Syntax Conventions
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.
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
