Monday, 6 August 2012

SQL Server : Lost all Administrator Account in SQL Server and Forgot sa Account Password

What will happen if you lost all your administrator account by mistake ? As per the best practice you have disabled the sa login. Those who had login with sysadmin rights left the company or not able to remember their password. Now you do not have any login with sysadmin rights. How do you get the administrative rights back?  

I had the same kind of scenario on a server which we use very rarely. Later we realized that we lost the administrative rights on that instance and we did not had any idea to get back the sysadmin rights. The only option was destroy the instance and rebuild it.But Microsoft thought about this scenario and kept an option to recover the sysadmin rights. Let us discuss this in detail through this post.

How it works

To solve this issue, we should know two sql server start up parameter 

-f Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.
-m Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. Typically, this option is used if you experience problems with system databases that should be repaired.
Enables the sp_configure allow updates option. By default, allow updates is disabled. Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

When we start SQL server in single user mode, it is very common that application which using this server might take the connection and you will not get the connection. To avoid this situation there is an option to give the application name along with the start up parameter. For example, -mSQLCMD will restrict the  connection only from an application named as SQLCMD. To connect only from SQMS, -mMicrosoft SQL Server Management Studio - Query Note that the application name is case sensitive.Any custom build application can change its name by tweaking the connection string(How to do it?)

Now we are going to connect the instance using windows authentication method . Make sure that your windows account is part of the local administrator group of the server on which SQL server resides. Follow the below steps :
  • Stop the sql server instance : This can be done by using the command prompt , SQL server configuration manager or services.msc. To stop from the command prompt, open the command prompt window with administrative rights(right click on the cmd icon  and select Run as administrator)  and run the command  net stop mssqlserver  for  default instance and for named instance                net stop smsqlserver$myinst replace myinst with your instance name.
  • To start the SQL server in single user mode, run the following command from command prompt.                                                       net start mssqlserver /mSQLCMD for named instance,net start mssqlserver$myinst /mSQLCMD   .You can do the same using the SQL server configuration manager by editing the startup parameter as given below and start the service from configuration manager.

  I personally prefer to do it from the command prompt,which gives more control. Make sure that you are mentioning the correct application name with proper case. if you give application name as sqlcmd, when you try to connect it from either from commad prompt or SSMS, it will throw an error :
Login failed for user 'Mydomain\loginname'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
The error itself is misleading. When i tried , I was getting this error and took some time to find out  the issue.

  • Now you can connect to the sql server from the command prompt and can create a login with sysadmin rights or can assign sysadmin rights to an existing login. To connect to the SQL  server from the command prompt , for default instance SQLCMD -Smyservername  for named instance   SQLCMD -Smyservername\myinstancename. Then you can use the following command to create a new login and adding into sysadmin role. 
CREATE login NewsysadminLogin WITH password='passwordtest123'
sp_addsrvrolemember 'NewsysadminLogin','sysadmin'

Now the login NewsysadminLogin will have the sysadmin rights on the server . Stop the service and start it .If you have modified the startup parameter through the configuration manager , do not forget to remove the singer user switch (-m) from there.

If you liked this post, do like my page on FaceBook


  1. Hi I followed this post and got no errors, but when i reopen SSMS i cant see the new login i created or login with it ? any ideas

    1. I do not have any worked well for me. Could you please check the server name/instance name in which you created the login and trying to connect

  2. What if the current windows account you logged in with was Disable in SQL too? And also sa is Disable
    Then I can not run SQLCMD and it gives me Login failed error

  3. Great article! We can also use some external tools to reset lost sa password or unlock a disabled sa account. Check out this video tutorial:

  4. You can recover SQL server password by running sqlcmd on the command prompt
    Then run the following transact-sql command

    EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';

    If the above solution fails then you take the help of SQL Password Recovery Tool:


  5. Thanks for posting this useful content, Good to know about new things here, Let me share this, . LINUX training in pune


  6. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune


  7. The blog or and best that is extremely useful to keep I can share the ideas
    of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    tanki online | 2048 game|

  8. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث

  9. شركة نقل عفش بالرياض وجدة والدمام والخبر والجبيل اولقطيف والاحساء والرياض وجدة ومكة المدينة المنورة والخرج والطائف وخميس مشيط وبجدة افضل شركة نقل عفش بجدة نعرضها مجموعة الفا لنقل العفش بمكة والخرج والقصيم والطائف وتبوك وخميس مشيط ونجران وجيزان وبريدة والمدينة المنورة وينبع افضل شركات نقل الاثاث بالجبيل والطائف وخميس مشيط وبريدة وعنيزو وابها ونجران المدينة وينبع تبوك والقصيم الخرج حفر الباطن والظهران
    شركة نقل عفش بجدة
    شركة نقل عفش بالمدينة المنورة
    شركة نقل اثاث بالرياض
    شركة نقل عفش بالدمام
    شركة نقل عفش بالطائف

  10. Thanks a lot Nelson! Worked like a charm!


  11. شركة اصباغ الكويت شركات الدهانات الكويت
    شراء اثاث مستعمل الكويت شركة شراء اثاث مستعمل الكويت
    فني كهربائي منازل الكويت كهربائي منازل بالكويت
    شركة مكافحة البق الكويت افضل شركة مكافحة البق الكويت
    شركة مكافحة حشرات الكويت افضل شركة مكافحة الحشرات الكويت
    نقل عفش مبارك الكبير شركة نقل عفش مبارك الكبير

  12. Password security is increasingly becoming a major concern for people and businesses on a personal and professional level. Many people use Free Random Password Generator, because they can not remember their own password. Anyways Keeping your systems, devices and networks secure with strong passwords is an absolute must today.

  13. I drink 2 litres of flavoured milk every day and 2 protein shakes each with 40g protein. war machines hack

  14. I drink 2 litres of flavoured milk every day and 2 protein shakes each with 40g protein. war machines hack

  15. zombie gunship survival hack Ur editing is next level man. I hv also seen brawl stars by ea. But this is fantastic

  16. mini golf king hack I am a BIG FAN of getting up at 5AM. It's perfect for quiet time, CREATIVE time and connection time. I hope to make videos as GREAT as yours in the near future. (I just created my first 7 day EPIC LIFE challenge

  17. Thank you for this, definitely will try out some of these just to try getting a summer body for our August beach trip!
    war machines hack

  18. After a year, analysing all kinds of diets, finally understood that we should eat normal foods that are grown locally from our native home town... no packed or imported foods. This is the trick but nobody ill like this comment!! factory inc hack

  19. it's working fine for me! Thank you for sharing this with us dominations hack

  20. Hey everyone I am so excited about this tutorial because it has helped me to I really appreciate thanks for sharing horse riding tales hack

  21. Aaditri Technology is a leading website design, web development company in Delhi, India we offer custom website development and all types of digital marketing services.
    SMO Service in Delhi
    Magento Development Company in Delhi
    Best cms development code in india

  22. Gym and Fitness equipment store in India - Buy best quality Exercise & Fitness equipment's online for lowest price at

    buy dumbbell online in india
    online sports and fitness shop in india
    buy fitness equipments online
    buy sports goods online

  23. Book online best pandit for all kinds of pooja program, Graha Shanti, festivals pooja, and all your religious programs from

    Book Pandit Ji Online
    Astrologer in Laxmi Nagar
    Pandit ji for Satyanarayan Katha in Vaishali

  24. Thank you for your valuable and useful information through the blog. I am appreciating with the way you shared the relevant, precious, and perfect information. Furthermore, I would like to also keep some sound knowledge of SEO COMPANY IN DELHI . Trionfo IT Services is one of the leading Digital marketing and Seo agency. We are located in Delhi If you Have Need of SEO Service Then Visit our Website you will get complete information related to SEO Services.