| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_helpdbfixedrole (Transact-SQL) |
sp_helpdbfixedrole returns a list of the fixed database roles. |
markingmyname |
maghan |
randolphwest |
05/14/2024 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Returns a list of the fixed database roles.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_helpdbfixedrole [ [ @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.
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 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. |
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.
The following example shows a list of all fixed database roles.
EXEC sp_helpdbfixedrole;
GO