Displays the permissions for each fixed database role.
sp_dbfixedrolepermission [[@rolename =] 'role']
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. These are 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 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 permissions of the db_owner fixed database role span all of the other fixed database roles. To display the permissions for fixed server roles, execute sp_srvrolepermission.
The permissions listed in the result set include the Transact-SQL statements that can be executed, as well as other special activities that can be performed by members of the database role.
All users have permissions to execute sp_dbfixedrolepermission.
This example displays the permissions for all fixed database roles.
EXEC sp_dbfixedrolepermission
| sp_addrolemember | sp_srvrolepermission |
| sp_droprolemember | System Stored Procedures |
| sp_helpdbfixedrole |