Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_helpdbfixedrole (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a list of the fixed database roles.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_helpdbfixedrole [ [ @rolename = ] 'role' ] 

[ @rolename = ] 'role'

Is the name of a fixed database role. role is sysname, with a default of NULL. If role is specified, only information about that role is returned; otherwise, a list and description of all fixed database roles is returned.

0 (success) or 1 (failure)

Column name

Data type

Description

DbFixedRole        

sysname

Name of the fixed database role.

Description       

nvarchar(70)

Description of DbFixedRole.

Fixed database roles, as shown in the following table, are defined at the database level and have permissions to perform specific database-level administrative activities. Fixed database roles cannot be added or removed. The permissions granted to a fixed database role cannot be changed.

Fixed database role

Description

db_owner

Database owners

db_accessadmin

Database access administrators

db_securityadmin 

Database security administrators

db_ddladmin

Database DDL administrators

db_backupoperator 

Database backup operators

db_datareader

Database data readers

db_datawriter 

Database data writers

db_denydatareader 

Database deny data readers

db_denydatawriter

Database deny data writers

The following table shows stored procedures that are used for modifying database roles.

Stored procedure

Action

sp_addrolemember 

Adds a database user to a fixed database role.

sp_helprole 

Displays a list of the members of a fixed database role.

sp_droprolemember 

Removes a member from a fixed database role.

Requires membership in the public role.

Information returned is subject to restrictions on access to metadata. Entities on which the principal has no permission do not appear. For more information, see Metadata Visibility Configuration.

The following example shows a list of all fixed database roles.

EXEC sp_helpdbfixedrole;
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft