sp_dbfixedrolepermission (Transact-SQL)


Displays the permissions of a fixed database role. sp_dbfixedrolepermission returns correct information in SQL Server 2000. The output does not reflect the changes to the permissions hierarchy that were implemented in SQL Server 2005. For more information, seePermissions (Database Engine).

System_CAPS_ICON_important.jpg Important

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_dbfixedrolepermission [ [ @rolename = ] 'role' ]  

[ @rolename = ] 'role'
Is the name of a valid 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.

0 (success) or 1 (failure)

Column nameData typeDescription
DbFixedRolesysnameName of the fixed database role
Permissionnvarchar(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 roleDescription
db_ownerDatabase owners
db_accessadminDatabase access administrators
db_securityadminDatabase security administrators
db_ddladminDatabase data definition language (DDL) administrators
db_backupoperatorDatabase backup operators
db_datareaderDatabase data readers
db_datawriterDatabase data writers
db_denydatareaderDatabase deny data readers
db_denydatawriterDatabase 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.

Requires membership in the public role.

The following query returns the permissions for all fixed database roles because it does not specify a fixed database role.

EXEC sp_dbfixedrolepermission;  

Security Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL)
sp_droprolemember (Transact-SQL)
sp_helpdbfixedrole (Transact-SQL)
sp_srvrolepermission (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions