sp_addsrvrolemember

Transact-SQL Reference

Transact-SQL Reference

sp_addsrvrolemember

Adds a login as a member of a fixed server role.

Syntax

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

Arguments

[@loginame =] 'login'

Is the name of the login being added to the fixed server role. login is sysname, with no default. login can be a Microsoft® SQL Server™ login or a Microsoft Windows NT® user account. If the Windows NT login has not already been granted access to SQL Server, access is granted automatically.

[@rolename =] 'role'

Is the name of the fixed server role in which the login is being added. role is sysname, with a default of NULL, and must be one of these values:

  • sysadmin

  • securityadmin

  • serveradmin

  • setupadmin

  • processadmin

  • diskadmin

  • dbcreator

  • bulkadmin
Return Code Values

0 (success) or 1 (failure)

Remarks

When a login is added to a fixed server role, the login gains the permissions associated with that fixed server role.

The role membership of the sa login cannot be changed.

Use sp_addrolemember to add a member to a fixed database or user-defined role.

sp_addsrvrolemember stored procedure cannot be executed within a user-defined transaction.

Permissions

Members of the sysadmin fixed server role can add members to any fixed server role. Members of a fixed server role can execute sp_addsrvrolemember to add members only to the same fixed server role.

Examples

This example adds the Windows NT user Corporate\HelenS to the sysadmin fixed server role.

EXEC sp_addsrvrolemember 'Corporate\HelenS', 'sysadmin'

See Also

sp_addrolemember

sp_dropsrvrolemember

System Stored Procedures