sp-droprolemember-transact-sql.md

---

title: "sp_droprolemember (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/20/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.service: "" ms.component: "system-stored-procedures" ms.reviewer: "" ms.suite: "sql" ms.technology:

• "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
• "sp_droprolemember_TSQL"
• "sp_droprolemember" dev_langs:
• "TSQL" helpviewer_keywords:
• "sp_droprolemember" ms.assetid: c2f19ab1-e742-4d56-ba8e-8ffd40cf4925 caps.latest.revision: 39 author: "edmacauley" ms.author: "edmaca" manager: "craigg" ms.workload: "On Demand" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

---

sp_droprolemember (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

Removes a security account from a [!INCLUDEssNoVersion] role in the current database.

Important

[!INCLUDEssNoteDepFutureAvoid] Use ALTER ROLE instead.

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
sp_droprolemember [ @rolename = ] 'role' ,   
     [ @membername = ] 'security_account'  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
sp_droprolemember 'role' ,  
     'security_account'  

Arguments

[ @rolename = ] 'role'
Is the name of the role from which the member is being removed. role is sysname, with no default. role must exist in the current database.

[ @membername = ] 'security_account'
Is the name of the security account being removed from the role. security_account is sysname, with no default. security_account can be a database user, another database role, a Windows login, or a Windows group. security_account must exist in the current database.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_droprolemember removes a member from a database role by deleting a row from the sysmembers table. When a member is removed from a role the member loses any permissions it has by membership in that role.

To remove a user from a fixed server role, use sp_dropsrvrolemember. Users cannot be removed from the public role, and dbo cannot be removed from any role.

Use sp_helpuser to see the members of a [!INCLUDEssNoVersion] role, and use ALTER ROLE to add a member to a role.

Permissions

Requires ALTER permission on the role.

Examples

The following example removes the user JonB from the role Sales.

EXEC sp_droprolemember 'Sales', 'Jonb';  

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

The following example removes the user JonB from the role Sales.

EXEC sp_droprolemember 'Sales', 'JonB'  

See Also

Security Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL)
sp_droprole (Transact-SQL)
sp_dropsrvrolemember (Transact-SQL)
sp_helpuser (Transact-SQL)
System Stored Procedures (Transact-SQL)