Team blog of MCS @ Middle East and Africa

This blog is created by Microsoft MEA HQ near shoring team, and it aims to share knowledge with the IT community.With its infrastructure and development sides,It brings to you the proven best practices and real world experiences from Subject Matter Experts
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

Cannot Login to SQL Server using administrator account

Cannot Login to SQL Server using administrator account

  • Comments 8
  • 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
  • ms sql server 2008 R2, It sows me login window on execution of Alter query, Can you me- ZAHID

  • Stupid! doesn't work on SQL Server 2008 +

  • 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

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

  • its work fine on 2012 , thnax alot :D

  • it doest work on any server

  • 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.

  • Thanks for sharing! Helpful post!

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