sp_helpdbfixedrole (Transact-SQL)


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 nameData typeDescription
DbFixedRolesysnameName of the fixed database role.
Descriptionnvarchar(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 roleDescription
db_ownerDatabase owners
db_accessadminDatabase access administrators
db_securityadminDatabase security administrators
db_ddladminDatabase DDL administrators
db_backupoperatorDatabase backup operators
db_datareaderDatabase data readers
db_datawriterDatabase data writers
db_denydatareaderDatabase deny data readers
db_denydatawriterDatabase deny data writers

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

Stored procedureAction
sp_addrolememberAdds a database user to a fixed database role.
sp_helproleDisplays a list of the members of a fixed database role.
sp_droprolememberRemoves 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;  

Security Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL)
sp_dbfixedrolepermission (Transact-SQL)
sp_droprolemember (Transact-SQL)
sp_helprole (Transact-SQL)
sp_helprolemember (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions