Removes a role from the current database. sp_dropgroup is provided for backward compatibility. In Microsoft® SQL Server™ version 7.0, groups are implemented as roles.
sp_dropgroup [ @rolename = ] 'role'
[@rolename =] 'role'
Is the role to remove from the current database. role is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
|Column name||Data type||Description|
|Name||sysname||The name of the existing member of the role.|
sp_dropgroup calls sp_droprole with the role value to remove the role. The public, fixed server, fixed database, or application roles cannot be removed. Use sp_dropapprole to remove an application role.
A role cannot be removed if it owns any objects. Either remove the objects before removing the role, or use sp_changeobjectowner to change the owner of any objects that must not be removed.
Additionally, the role cannot be removed if there are any members of the role. Use sp_droprolemember to remove the user from the role. If any users are still members of the role, sp_dropgroup displays those members.
sp_dropgroup cannot be executed within a user-defined transaction.
Only members of the sysadmin fixed server role, the db_securityadmin or db_owner fixed database roles, or the owner of the role, can execute sp_dropgroup.
This example removes the role my_role from the current database.
EXEC sp_dropgroup 'my_role'