Skip to content

Latest commit

 

History

History
90 lines (65 loc) · 2.98 KB

sp-dropsrvrolemember-transact-sql.md

File metadata and controls

90 lines (65 loc) · 2.98 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_dropsrvrolemember (Transact-SQL)
sp_dropsrvrolemember removes a SQL Server login, Windows user, or Windows group, from a fixed server role.
VanMSFT
vanto
randolphwest
11/28/2023
sql
system-objects
reference
sp_dropsrvrolemember
sp_dropsrvrolemember_TSQL
sp_dropsrvrolemember
TSQL

sp_dropsrvrolemember (Transact-SQL)

[!INCLUDE SQL Server]

Removes a [!INCLUDE ssNoVersion] login, a Windows user, or Windows group, from a fixed server role.

Important

[!INCLUDE ssNoteDepFutureAvoid] Use ALTER SERVER ROLE instead.

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

Syntax

sp_dropsrvrolemember
    [ @loginame = ] N'loginame'
    [ , [ @rolename = ] N'rolename' ]
[ ; ]

Arguments

[ @loginame = ] N'loginame'

The name of a login to remove from the fixed server role. @loginame is sysname, with no default. @loginame must exist.

[ @rolename = ] N'rolename'

The name of a server role. @rolename is sysname, with a default of NULL. @rolename must be one of the following values:

  • sysadmin
  • securityadmin
  • serveradmin
  • setupadmin
  • processadmin
  • diskadmin
  • dbcreator
  • bulkadmin

Return code values

0 (success) or 1 (failure).

Remarks

Only sp_dropsrvrolemember can be used to remove a login from a fixed server role. Use sp_droprolemember to remove a member from a database role.

The sa login can't be removed from any fixed server role.

sp_dropsrvrolemember can't be executed within a user-defined transaction.

Permissions

Requires membership in the sysadmin fixed server role, or both ALTER ANY LOGIN permission on the server, and membership in the role from which the member is being dropped.

Examples

The following example removes the login JackO from the sysadmin fixed server role.

EXEC sp_dropsrvrolemember 'JackO', 'sysadmin';

Related content