Returns a list of the SQL Server fixed server roles.
sp_helpsrvrole [ [ @srvrolename = ] 'role' ]
0 (success) or 1 (failure)
|
Column name |
Data type |
Description |
|---|---|---|
|
ServerRole |
sysname |
Name of the server role |
|
Description |
sysname |
Description of ServerRole |
Fixed server roles are defined at the server level and have permissions to perform specific server-level administrative activities. Fixed server roles cannot be added, removed, or changed.
To add or removed members from server roles, see ALTER SERVER ROLE (Transact-SQL).
All logins are a member of public. sp_helpsrvrole does not recognize the public role because, internally, SQL Server does not implement public as a role.
sp_helpsrvrole does not take a user-defined server role as an argument. To list the user-defined server roles, see the examples in ALTER SERVER ROLE (Transact-SQL).
Requires membership in the public role.
A. Listing the fixed server roles
The following query returns the list of fixed server roles.
EXEC sp_helpsrvrole ;
B. Listing fixed and user-defined server roles
The following query returns a list of both fixed and user-defined server roles.
SELECT * FROM sys.server_principals WHERE type = 'R' ;
C. Returning a description of a fixed server role
The following query returns the name and description of the diskadmin fixed server roles.
sp_helpsrvrole 'diskadmin' ;