sp-dropsrvrolemember-transact-sql.md

title	sp_dropsrvrolemember (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/20/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sp_dropsrvrolemember sp_dropsrvrolemember_TSQL
dev_langs	TSQL
helpviewer_keywords	sp_dropsrvrolemember
ms.assetid	7be99181-d221-49d0-9cb2-c930d8c044a0
caps.latest.revision	20
author	BYHAM
ms.author	rickbyh
manager	jhubbard

sp_dropsrvrolemember (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

Removes a [!INCLUDEssNoVersion] login or a Windows user or group from a fixed server role.

Important

[!INCLUDEssNoteDepFutureAvoid] Use ALTER SERVER ROLE instead.

Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version).

Transact-SQL Syntax Conventions

Syntax

  
sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role'  

Arguments

[ @loginame = ] 'login'
Is the name of a login to remove from the fixed server role. login is sysname, with no default. login must exist.

[ @rolename = ] 'role'
Is the name of a server role. role is sysname, with a default of NULL. role 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 cannot be removed from any fixed server role.

sp_dropsrvrolemember cannot 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';  

See Also

CREATE SERVER ROLE (Transact-SQL)
DROP SERVER ROLE (Transact-SQL)
Security Stored Procedures (Transact-SQL)
sp_addsrvrolemember (Transact-SQL)
sp_droprolemember (Transact-SQL)
System Stored Procedures (Transact-SQL)
Security Functions (Transact-SQL)