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

    Question by:
    On

    Topics:

    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

    Good Question?
    0
     

    ?

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

    Accepted Solution 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.

    Top Expert Contributor

    Essential articles and videos from the Experts

    More valuable questions with Expert answers

    201511-LO-Qu-074

    Extend your technology team with the Experts Exchange community.

    — trusted by —

    Who answers my questions?Our community has technology experts around the world.

    Vitor Montalvão

    11

    Articles

    2,155

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005
    • Query Syntax
    • Databases-Other

    Brian Crowe

    1,661

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • Visual Basic.NET
    • Query Syntax
    • Crystal Reports

    Paul Maxwell

    9

    Articles

    2,788

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • Query Syntax
    • MS SQL Server 2005
    • Oracle Database

    ScottPletcher

    8,626

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005
    • Query Syntax
    • Databases-Other

    Jim Horn

    18

    Articles

    10,529

    Solutions

    Expert in:

    • MS Access
    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005
    • Query Syntax

    Zberteoc

    907

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2005
    • MS SQL Server 2008
    • Query Syntax
    • Databases-Other

    Olaf Doschke

    2

    Articles

    1,187

    Solutions

    Expert in:

    • FoxPro
    • MS SQL Server
    • Databases-Other
    • MS Applications
    • MS SQL Server 2005

    Éric Moreau

    2

    Articles

    10,369

    Solutions

    Expert in:

    • .NET Programming
    • Visual Basic.NET
    • C#
    • Visual Basic Classic
    • ASP.NET

    geek_vj

    306

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2005
    • MS SQL Server 2008

    RELATED TOPICS view all topics

    1. MS SQL Server 2005
      (71,596)
    2. MS SQL Server 2008
      (48,092)
    3. Query Syntax
      (46,103)
    4. Databases-Other
      (53,009)
    5. .NET Programming
      (131,135)
    6. MS Access
      (214,064)
    7. MS Development-Other
      (48,696)
    8. ASP.NET
      (122,117)
    9. SSRS
      (8,524)
    10. Visual Basic.NET
      (91,453)