Question

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

Asked by: dfxdeimos

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

This question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Sign Up Now!
Asked On
2009-02-20 at 10:58:37ID: 24162948
Tags

SQL 2008

,

sysadmin

Topics

MS SQL Server

,

SQL Server 2008

Participating Experts
2
Points
100
Comments
8

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

Companies like CVS, Honeywell, Chevron, Toyoto, TriStar Pictures, the U.S. Army, and Accenture

Your Complete Technology Resource

900+ topic areas (and counting)

Related Solutions

  1. SysAdmin Role and Groups
    I have groups created in NT, and set up in SQL 7. Can I set a group as SysAdmin Role for just a particular database? Or, does the SysAdmin Role remain for any database the group has access to? What suggestions would you have to limit...
  2. SQL7 RAISERROR with log: Why is sysadmin s…
    If user X with no privs to anything trys to use RAISERROR WITH LOG the below error message is returned since user X does not belong to sysadmin: Server: Msg 118, Level 15, State 1, Line 1 Only members of the sysadmin role can specify t...
  3. BUILTIN\Administrator on SYSADMIN Role for S…
    I was working on a fresh install of SQL7 SP4 on an NT4 SP6a server. Going through the MBSA on some recommended fixes I removed the BUILTIN\Administrator account from the SYSADMIN ROLE. I guess the morning coffee had not kicked in yet...
  4. sa account doesn't have sysadmin access
    Hi, I have a problem with our SQL 2000 server installed on SBS 2003. We use Maximizer as a CRM software, this creates its database in MS SQL. Recently, I have not been able to create any accounts through the Maximizer software because ...
Tutorials are great ways to tackle hard problems!

Featured Video Tutorials

Microsoft Access Tutorials

More Technology Tutorials

View All Tutorials

Featured Articles

Experts Exchange Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.

It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul.

We love the accolades. We love to be seen as the digital white knight who fixed the server, the computer, the email, and anything else that life depends on.

We love it so much, we sometimes make horrible decisions. Sometimes, we work "FOR FREE."

Experts Exchange Articles

SCCM OSD Basic Troubleshooting

SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispersed nature of the blog posts that deal with some of these issues. This article will help SCCM OSD newbies with some of these issues.

Integrating SCCM and MDT 2008.

For our deployments, we always install MDT 2008 on our SCCM server and select Configure ConfigMgr Integration from the MDT area on the start menu. This gives us:

  • Templated task sequences we can import into SCCM
  • Additional TS Variables for use in our task sequences
  • Additional options and flexibility around computer backup...
Read through articles written by top experts!

More Articles

Read More Articles

Get full access to the help you need.

Subscribe Now

30-day free trial. Register in 60 seconds.

The Latest Technology News and Tips

Your answer is only minutes away!

New Solutions Everyday

Because of our active community of experts, on average new questions receive their first comment in under 30 minutes.

Top MS SQL Server Questions

View More

View More

       

Hear What our Users are Saying

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.
20130221-EE-VQP-038

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Development.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Microsoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...an excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans