Technical FAQs

Ask a Question

A query to determine whether a SQL Server login is a member of the specified server role

Issue
How to determine if the current (or any) user can perform an action requiring the server role's permissions.

Product Line
SQL Server
Database Manager (PME 8.2)

Environment
SQL Server Management Studio (SSMS)

Cause
A function that can determine if the user belongs to a server role.

Important: Database Manager uses the same function to check if the login has sysadmin role before opening the application. If the login does not have sysadmin role, it will not open.

Resolution
The syntax of a function that returns if the login is part of the specified server role:
IS_SRVROLEMEMBER ( 'role' [ , 'login' ] )

role: Is the name of the server role that is being checked. 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.

Return Value:
0: login is not a member of role.
1: login is a member of role.
NULL: role or login is not valid.


Usage examples:

SELECT IS_SRVROLEMEMBER('sysadmin','ION')

SELECT IS_SRVROLEMEMBER('public', 'report')

SELECT IS_SRVROLEMEMBER('sysadmin','any_dmain\any_user')
The above query takes a domain user as the login

SELECT IS_SRVROLEMEMBER('sysadmin')
The above query takes the current user as the login

 
Was this helpful?
What can we do to improve the information ?