' 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 login to check. login is sysname, with a default of NULL. If no value is specified, the result will be based on the current Execution context.

int

IS_SRVROLEMEMBER returns the following values.

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

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

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. If the user specified is not the current user, the value returned by IS_SRVROLEMEMBER might differ from the authenticator's (such as Active Directory) last data refresh to SQL Server.

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. For more information about User Account Control, see How to: Connect to SQL Server from Windows Vista.

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.'

Reference

Other Resources

Help and Information