Skip to content

Latest commit

 

History

History
101 lines (70 loc) · 3.68 KB

sp-droprolemember-transact-sql.md

File metadata and controls

101 lines (70 loc) · 3.68 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sp_droprolemember (Transact-SQL)
sp_droprolemember removes a security account from a SQL Server role in the current database.
VanMSFT
vanto
randolphwest
08/21/2024
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 [!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

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]

Arguments

[ @rolename = ] N'rolename'

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.

[ @membername = ] N'membername'

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.

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 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.

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: [!INCLUDE ssazuresynapse-md] and [!INCLUDE ssPDW]

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

EXEC sp_droprolemember 'Sales', 'JonB'

Related content