How do I add a login to the "sysadmin" server role?

MS SQL Server Question

I am having trouble with the following scenario:

Windows Server 2008 Enterprise Edition, SQL Server 2008 Enterprise Edition, and Team Foundation Server all running on one machine.

I am trying to create a maintenance plan to backup the TFS databases. In order to do that I have to be able to work in the SQL Management Studio with an account that is a member of the "sysadmin" server role.

The only member of the "sysadmin" server role is the "SA" account. The "SA" account password was left blank during install. When I try to add a new login (my domain admin account in this case) to the "sysadmin" server role I recieve the error message noted in "ERROR MESSAGE 1" below.

If I try to log into the SQL Management Studio with the SA account via the SQL Authentication mode I receive the error message noted in "ERROR MESSAGE 2" below.

My goal is to either:

(A) Add another login to the "sysadmin" role
or
(B) Login to the SQL Management Studio with the "SA" account

Please help! Thank you.

ERROR MESSAGE 1:
 
Add member failed for ServerRole 'sysadmin'. (Microsoft SqlServer.Smo).
|_  An Exception occurred while executing a Transact-SQL statement or batch.
    (Microsoft.SqlServer.ConnectionInfo)
    |_ User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
 
ERROR MESSAGE 2:
 
Cannot connect to <INSTANCENAME>
|_ Login failed for user 'SA'. (Microsoft SQL Server, Error: 18456)

                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Select allOpen in new window

 

Verified Answer?

The member who asked this question verified this comment provided the solution that solved their problem.

by:Posted on 2009-02-20 at 12:46:20ID: 23695995

No, the ONLY member of the "sysadmins" group is the "SA" account.

The "SA" account has it's login abilities disabled.

What it seems like I am going to have to do is to start cycle the service and put it in matainence mode and then use a local admin account to run a "sp_addsrvrolemember" command.

This content is available to Experts Exchange members

See the answer now
with your Free 30 Day Trial

Get unlimited access to solutions & experts

  • 4,169,477 solved questions
  • 3,805 articles & videos
  • 15,413 tech experts

Get Access Now

Need a customized answer?
Ask your question for one-on-one assistance. We will email you when an expert has commented on your question.

We will never share this with anyone.

201505-LO-Qu-057

Related Articles

Related Questions

Experts Exchange powers the growth and success
of technology professionals worldwide.

  • Solve

    Experts Exchange is the tech professional’s trusted, on-demand resource for solving difficult problems, making informed decisions, and delivering excellent solutions.

  • Learn

    With unparalleled access to technical experts, verified real-world solutions, and diverse educational content, Experts Exchange enables personalized development of technology skills.

  • Network

    Experts Exchange gives you the professional exposure and valued relationships key to building the career you want.

Join the Network Today

See Plans and Pricing