How to add a Windows user to the sysadmin server role in SQL Server 2012?

Question

I have a Powershell script that runs in the context of a Windows user. This user has a login on an instance of SQL 2012, which is a member of the sysadmin server role.

I want the script to add another login to the SQL instance (which the corresponding Windows user also has) to the sysadmin server role.

To try this in Management Studio (connected as SA), I use the following script:

execute as user = 'domain\currentsysadmin' IF IS_SRVROLEMEMBER('sysadmin', 'domain\futuresysadmin') != 1 ALTER SERVER ROLE [sysadmin] ADD MEMBER [domain\futuresysadmin] revert 

I have two questions:

• When I run the script as it appears here, calling IS_SRVROLEMEMBER returns 0 as expected, but even if the domain login \ currentsysadmin is a member of sysadmin, calling ALTER SERVER ROLE returns:

  Msg 15151, Level 16, State 1, Line 8 It is not possible to change the server role 'sysadmin' because it does not exist or you do not have permission.

• When I run this in the context of SA without the EXECUTE AS statement, the ALTER SERVER ROLE statement works successfully, but when I check the domain login \ futuresysadmin, it is still not a member of the sysadmin role

How to add domain login \ futuresystem to the sysadmin server role?

Answer 1

Use sp_addsrvrolemember :

 EXEC master..sp_addsrvrolemember @loginame = N'domain\futuresysadmin', @rolename = N'sysadmin' 

UPDATE: User context switching does not inherit server roles, such as sysadmin. When reading the documentation: "Server-level permissions granted explicitly to identifiers in a user token or through membership in roles are not respected." When you switch the user context, your user no longer has the right to grant sysadmin to another user.