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'