sp_dbfixedrolepermission (Transact-SQL)
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 does not 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 it's 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
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
See Also
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)
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: I hele 2024 udfaser vi GitHub-problemer som feedbackmekanisme for indhold og erstatter det med et nyt feedbacksystem. Du kan få flere oplysninger under:Indsend og få vist feedback om