| title | sp_droprolemember (Transact-SQL) | ||
|---|---|---|---|
| description | sp_droprolemember removes a security account from a SQL Server role in the current database. | ||
| author | VanMSFT | ||
| ms.author | vanto | ||
| ms.reviewer | randolphwest | ||
| ms.date | 06/23/2025 | ||
| ms.service | sql | ||
| ms.subservice | system-objects | ||
| ms.topic | reference | ||
| ms.custom |
|
||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| dev_langs |
|
||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricsqldb]
Removes a security account from a [!INCLUDE ssNoVersion] role in the current database.
Important
[!INCLUDE ssNoteDepFutureAvoid] Use ALTER ROLE instead.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
Syntax for [!INCLUDE ssnoversion-md] and [!INCLUDE ssazurede-md].
sp_droprolemember
[ @rolename = ] N'rolename'
, [ @membername = ] N'membername'
[ ; ]
Syntax for Azure Synapse Analytics and Analytics Platform System (PDW).
sp_droprolemember N'rolename' , 'membername'
[ ; ]
Note
[!INCLUDE synapse-analytics-od-unsupported-syntax]
The name of the role from which the member is being removed. @rolename is sysname, with no default. @rolename must exist in the current database.
The name of the security account being removed from the role. @membername is sysname, with no default. @membername can be a database user, another database role, a Windows account, or a Windows group. @membername must exist in the current database.
0 (success) or 1 (failure).
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 can't be removed from the public role, and dbo can't be removed from any role.
Use sp_helpuser to see the members of a [!INCLUDE ssNoVersion] role, and use ALTER ROLE to add a member to a role.
Requires ALTER permission on the role.
The following example removes the user JonB from the role Sales.
EXECUTE sp_droprolemember 'Sales', 'Jonb';The following example removes the user JonB from the role Sales.
EXECUTE sp_droprolemember 'Sales', 'JonB';