sp_dbfixedrolepermission
Displays the permissions for each fixed database role.
Syntax
sp_dbfixedrolepermission [ [ @rolename = ] 'role' ]
Arguments
[@rolename =] 'role'
Is the name of a valid Microsoft® SQL Server™ fixed database role. role is sysname, with a default of NULL. If role is not specified, the permissions for all fixed database roles are displayed.
Return Code Values
0 (success) or 1 (failure)
Result Sets
| Column name | Data type | Description |
|---|---|---|
| DbFixedRole | sysname | Name of the fixed database role |
| Permission | nvarchar(70) | Permissions associated with DbFixedRole |
Remarks
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.
Permissions
All users have permissions to execute sp_dbfixedrolepermission.
Examples
This example displays the permissions for all fixed database roles.
EXEC sp_dbfixedrolepermission