| description | sp_droprole (Transact-SQL) | ||
|---|---|---|---|
| title | sp_droprole (Transact-SQL) | Microsoft Docs | ||
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 889ee074-00f8-40a9-bddb-d7d3ef0cbc19 | ||
| author | markingmyname | ||
| ms.author | maghan |
[!INCLUDE SQL Server]
Removes a database role from the current database.
Important
In [!INCLUDEssVersion2005], sp_droprole was replaced by the DROP ROLE statement. sp_droprole is included only for compatibility with earlier versions of [!INCLUDEssNoVersion] and may not be supported in a future release.
Transact-SQL Syntax Conventions
sp_droprole [ @rolename= ] 'role'
[ @rolename = ] 'role'
Is the name of the database role to remove from the current database. role is a sysname, with no default. role must already exist in the current database.
0 (success) or 1 (failure)
Only database roles can be removed by using sp_droprole.
A database role with existing members cannot 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 cannot be removed.
A role cannot 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 cannot be executed within a user-defined transaction.
Requires CONTROL permission on the role.
The following example removes the application role Sales.
EXEC sp_droprole 'Sales';
GO
Security Stored Procedures (Transact-SQL)
sp_addrole (Transact-SQL)
DROP ROLE (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
sp_dropapprole (Transact-SQL)
System Stored Procedures (Transact-SQL)