Share via

IS_SRVROLEMEMBER (Transact-SQL)

  • Article

Indicates whether a SQL Server 2005 login is a member of the specified fixed server role.

Topic link iconTransact-SQL Syntax Conventions

Syntax

IS_SRVROLEMEMBER ('role' [ , 'login' ] )

Arguments

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

    Valid values for role include the following:

    sysadmin

    • dbcreator
    • bulkadmin
    • diskadmin
    • processadmin
    • serveradmin
    • setupadmin
    • securityadmin
  • 'login'
    Is the name of the SQL Server 2005 login to check. login is sysname, with a default of NULL. If no value is specified, the SQL Server login of the current user is used.

Return Types

int

Remarks

IS_SRVROLEMEMBER returns the following values.

Return value Description

0

login is not a member of role.

1

login is a member of role.

NULL

role or login is not valid.

Use this function to determine whether the current user can perform an action requiring the server role's permissions.

If a Microsoft Windows login, such as London\JoeB, is specified for login, IS_SRVROLEMEMBER returns NULL, unless the login has been granted or denied direct access to SQL Server 2005.

If login is a Windows domain login, it may be a member of a fixed server role through membership in a Windows group. To resolve such indirect memberships, IS_SRVROLEMEMBER requests Windows group membership information from the domain controller. If the domain controller is not accessible or does not respond, IS_SRVROLEMEMBER returns role membership information taking into account the user and its local groups only.

Examples

The following example indicates whether the SQL Server login for the current user is a member of the sysadmin fixed server role.

IF IS_SRVROLEMEMBER ('sysadmin') = 1
   print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
   print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
   print 'ERROR: The server role specified is not valid.'

See Also

Reference

IS_MEMBER (Transact-SQL)
Security Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance