sp_dropsrvrolemember

Transact-SQL Reference

Transact-SQL Reference

sp_dropsrvrolemember

Removes a Microsoft® SQL Server™ login or a Microsoft Windows NT® user or group from a fixed server role.

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 a valid fixed server role, and must be one of these 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 server role. Use sp_droprolemember to remove a member from a standard SQL Server role.

When a login has been removed from a server role, that login can no longer perform activities based on the permissions associated with the server role.

The sa login cannot be removed from any fixed server role.

sp_dropsrvrolemember cannot be executed from within a user-defined transaction.

Permissions

Only members of the sysadmin fixed server role can execute sp_dropsrvrolemember to remove any login from a fixed server role. Members of a fixed server role can remove other members of the same fixed server role.

Examples

This example removes the login JackO from the sysadmin fixed server role.

EXEC sp_dropsrvrolemember 'JackO', 'sysadmin'

See Also

sp_addsrvrolemember

sp_droprolemember

System Stored Procedures