Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

sp_helpsrvrole (Transact-SQL)

Returns a list of the SQL Server fixed server roles.

Topic link icon Transact-SQL Syntax Conventions

sp_helpsrvrole [ [ @srvrolename = ] 'role' ]
[ @srvrolename= ] 'role'

Is the name of the fixed server role. role is sysname, with a default of NULL. role can be one of the following values.

Fixed server role

Description

sysadmin

System administrators

securityadmin

Security administrators

serveradmin

Server administrators

setupadmin

Setup administrators

processadmin

Process administrators

diskadmin

Disk administrators

dbcreator

Database creators

bulkadmin

Can execute BULK INSERT statements

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' ;
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.