sp_dbfixedrolepermission

Transact-SQL Reference

Transact-SQL Reference

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

See Also

sp_addrolemember

sp_droprolemember

sp_helpdbfixedrole

sp_srvrolepermission

System Stored Procedures