Edit

Share via

sp_helpdbfixedrole (Transact-SQL)

  • Article

Applies to: SQL Server

Returns a list of the fixed database roles.

Transact-SQL syntax conventions

Syntax

sp_helpdbfixedrole [ [ @rolename = ] N'rolename' ]
[ ; ]

Arguments

[ @rolename = ] N'rolename'

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

Return code values

0 (success) or 1 (failure).

Result set

Column name Data type Description
DbFixedRole sysname Name of the fixed database role.
Description nvarchar(70) Description of DbFixedRole.

Remarks

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 can't be added or removed. The permissions granted to a fixed database role can't 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.

Permissions

Requires membership in the public role.

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

Examples

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

EXEC sp_helpdbfixedrole;
GO

Related content