MS SQL Server Question

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

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

Trusted by Thousands of Top Companies

201410-LO-Qu-022

Related Questions We have nearly 4 million solutions here.

See More MS SQL Server Solutions

Solve Your Difficult Tech Problems Faster with Experts Exchange

Access millions of verified solutions and get 1-on-1 help from the experts.

Try It Free

30 day free trial.

Experts Exchange gives me a day to day reference of proven solutions that provide me guidance and troubleshooting help for my own clients.

- Brian B. Forte Consulting

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