This topic has not yet been rated - Rate this topic

sp_dropsrvrolemember (Transact-SQL)

SQL Server 2012

Removes a SQL Server login or a Windows user or group from a fixed server role.

Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SERVER ROLE instead.

Topic link icon 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';

Reference

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)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD