sp-droprolemember-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
sp_droprolemember (Transact-SQL)	sp_droprolemember (Transact-SQL)	VanMSFT	vanto	03/20/2017	sql	system-objects	reference	sp_droprolemember_TSQL sp_droprolemember	sp_droprolemember	TSQL	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sp_droprolemember (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

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

[!IMPORTANT]
[!INCLUDEssNoteDepFutureAvoid] Use ALTER ROLE instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

Syntax for both SQL Server and Azure SQL Database

sp_droprolemember [ @rolename = ] 'role' ,   
     [ @membername = ] 'security_account'  

Syntax for both Azure Synapse Analytics and Parallel Data Warehouse

sp_droprolemember 'role' ,  
     'security_account'  

[!NOTE] [!INCLUDEsynapse-analytics-od-unsupported-syntax]

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: [!INCLUDEssazuresynapse-md] 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)