| title | sp_dbfixedrolepermission (Transact-SQL) | ||
|---|---|---|---|
| description | sp_dbfixedrolepermission displays the permissions of a fixed database role. | ||
| author | markingmyname | ||
| ms.author | maghan | ||
| ms.reviewer | randolphwest | ||
| ms.date | 06/23/2025 | ||
| ms.service | sql | ||
| ms.subservice | system-objects | ||
| ms.topic | reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| dev_langs |
|
[!INCLUDE SQL Server]
Displays the permissions of a fixed database role. sp_dbfixedrolepermission returns correct information in [!INCLUDE ssVersion2000]. The output doesn't reflect the changes to the permissions hierarchy that were implemented in [!INCLUDE ssVersion2005]. For more information, see Database-Level Roles, which shows a list of fixed database roles and its corresponding permissions.
Important
[!INCLUDE ssNoteDepFutureAvoid]
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_dbfixedrolepermission [ [ @rolename = ] N'rolename' ]
[ ; ]
The name of a valid [!INCLUDE ssNoVersion] fixed database role. @rolename is sysname, with a default of NULL. If @rolename isn't specified, the permissions for all fixed database roles are displayed.
0 (success) or 1 (failure).
| Column name | Data type | Description |
|---|---|---|
DbFixedRole |
sysname | Name of the fixed database role |
Permission |
nvarchar(70) | Permissions associated with DbFixedRole |
To display a list of the fixed database roles, execute sp_helpdbfixedrole. The following table shows the fixed database roles.
| Fixed database role | Description |
|---|---|
| db_owner | Database owners |
| db_accessadmin | Database access administrators |
| db_securityadmin | Database security administrators |
| db_ddladmin | Database data definition language (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 |
Members of the db_owner fixed database role have the permissions of all the other fixed database roles. To display the permissions for fixed server roles, execute sp_srvrolepermission.
The result set includes the [!INCLUDE tsql] statements that can be executed, and other special activities that can be performed, by members of the database role.
Requires membership in the public role.
The following query returns the permissions for all fixed database roles because it doesn't specify a fixed database role.
EXECUTE sp_dbfixedrolepermission;
GO