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
Microsoft SQL Server

Environment
SQL Server Management Studio (SSMS)
Database Manager (Power Monitoring Expert)

Cause
Certain actions within SQL require certain levels of permission granted by different server roles. The following query can determine if the user belongs to a given 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
Run this query to determine if the login is part of the specified server role:
SELECT 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

note: To get complete information about this Transact-SQL statement, click IS_SRVROLEMEMBER.

note: Refer to FA346468 for a related article.

게시 대상: 슈나이더 일렉트릭 Korea