Edit

Share via

sp_dbfixedrolepermission (Transact-SQL)

  • Article

Applies to: SQL Server

Displays the permissions of a fixed database role. sp_dbfixedrolepermission returns correct information in SQL Server 2000 (8.x). The output doesn't reflect the changes to the permissions hierarchy that were implemented in SQL Server 2005 (9.x). For more information, see Database-Level Roles, which shows a list of fixed database roles and its corresponding permissions.

Important

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Transact-SQL syntax conventions

Syntax

syntaxsql 
sp_dbfixedrolepermission [ [ @rolename = ] N'rolename' ]
[ ; ]

Arguments

[ @rolename = ] N'rolename'

The name of a valid SQL Server 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.

Return code values

0 (success) or 1 (failure).

Result set

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. 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 Transact-SQL statements that can be executed, and other special activities that can be performed, by members of the database role.

Permissions

Requires membership in the public role.

Examples

The following query returns the permissions for all fixed database roles because it doesn't specify a fixed database role.

SQL 
EXEC sp_dbfixedrolepermission;
GO

Related content