0 out of 3 rated this helpful - Rate this topic

sp_dbfixedrolepermission (Transact-SQL)

SQL Server 2005

Displays the permissions of a fixed database role.

Note:
sp_dbfixedrolepermission returns information that is correct in SQL Server 2000. The output does not reflect the changes to the permissions hierarchy that are implemented in SQL Server 2005. For more information, see Permissions of Fixed Database Roles.

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_dbfixedrolepermission [ [ @rolename = ] 'role' ]

Arguments

[ @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.

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. 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 does not specify a fixed database role.

EXEC sp_dbfixedrolepermission;
GO

Reference

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)

Help and Information

Getting SQL Server 2005 Assistance

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD