sp_droprole (Transact-SQL)
Applies to: SQL Server
Removes a database role from the current database.
Important
In SQL Server 2005 (9.x), sp_droprole
was replaced by the DROP ROLE statement. sp_droprole
is included only for compatibility with earlier versions of SQL Server and might not be supported in a future release.
Transact-SQL syntax conventions
Syntax
sp_droprole [ @rolename = ] N'rolename'
[ ; ]
Arguments
[ @rolename = ] N'rolename'
The name of the database role to remove from the current database. @rolename is sysname, with no default. @rolename must already exist in the current database.
Return code values
0
(success) or 1
(failure).
Remarks
Only database roles can be removed by using sp_droprole
.
A database role with existing members can't be removed. All members of a database role must be removed before the database role can be removed. To remove users from a role, use sp_droprolemember
. If any users are still members of the role, sp_droprole
displays those members.
Fixed roles and the public role can't be removed.
A role can't be removed if it owns any securables. Before dropping an application role that owns securables, you must first transfer ownership of the securables, or drop them. Use ALTER AUTHORIZATION
to change the owner of objects that must not be removed.
sp_droprole
can't be executed within a user-defined transaction.
Permissions
Requires CONTROL
permission on the role.
Examples
The following example removes the application role Sales
.
EXEC sp_droprole 'Sales';
GO
Related content
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