GD Bloggers

This is the blog site for Microsoft Global Delivery Communities focused in sharing the technical knowledge about devices, apps and cloud.
Visitor Globe
Search Blogs
Tags
Archive
Archives
Options
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

Cannot Login to SQL Server using administrator account

TechNet Blogs » GD Bloggers » Cannot Login to SQL Server using administrator account

Cannot Login to SQL Server using administrator account

Ahmed Nagy Ahmed Nagy
1 Dec 2011 8:50 PM
  • Comments 10
  • Likes

After installing SQL server on a machine, it happens that you connect or disconnect that machine to domain. WHen you do this, the administrator account can no longer access the database engine. The below steps allow you to regain access to the SQL server...

  1. Stop SQL Service: on the command line type: net stop MSSQLServer
  2. Start the SQL Server in Management mode: on the command line type: net start MSSQLServer /m
  3. Open the SQL Server management studio, cancel the login dialog
  4. Open new sql server engine query window: from the menu, Click file->new->Database engine query
  5. Enable SA account if not enabled: in the query window type: Alter login sa enable
  6.  Set the password of the sa account: alter login sa with password='my password'
  7. Stop the SQL server from the command line: net stop MSSQlServer
  8. Start SQL Service from the command line: net start mssqlserver
  9. Start the SQL Management studio and connect to the server using sa account
  10. Add you domain administrator as sysadmin
  11. Disable the sa account when you finish

 

Comments
Comments
  • Anonymous ZAHID
    7 Oct 2012 4:41 PM

    ms sql server 2008 R2, It sows me login window on execution of Alter query, Can you me- ZAHID

  • Anonymous Soli
    29 Mar 2013 5:18 PM

    Stupid! doesn't work on SQL Server 2008 +

  • Anonymous Syed
    29 Jul 2013 6:41 PM

    Its working on SQL Server 2008 R2

    Small update on step 1 and 3

    Run as administrator both command line and SQL Server Management Studio

  • Anonymous Ebonweaver
    6 Aug 2014 9:21 PM

    Bogus information, you can't do anything in the management studio without authenticating first.

  • Anonymous just me
    1 Sep 2014 2:28 AM

    its work fine on 2012 , thnax alot :D

  • Anonymous Velja
    9 Sep 2014 4:31 PM

    it doest work on any server

  • Anonymous George
    21 Sep 2014 8:36 PM

    This worked for me, just make sure you're starting SQL Server Management Studio as Administrator, and then authenticate using the domain admin account once in management mode.

  • Anonymous Sohail
    22 Sep 2014 10:05 AM

    Thanks for sharing! Helpful post!

  • Anonymous MTR
    14 Oct 2014 10:31 AM

    Thanks, it has worked for me. cheers.

  • Anonymous guest
    16 Dec 2014 11:03 PM

    Does work on 2012 box. Run as admin after and connect to local host using windows auth.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment