Pages

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. 
USE MASTER
GO
CREATE login NewsysadminLogin WITH password='passwordtest123'
GO
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

12 comments:

  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

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

      Delete
  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

    ReplyDelete
  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: https://www.youtube.com/watch?v=GfI-bo5TDB0

    ReplyDelete
  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';
    GO

    If the above solution fails then you take the help of SQL Password Recovery Tool: http://www.sqlmdfviewer.org/sql-password-recovery-freeware.html

    ReplyDelete
  5. Thanks a lot Nelson! Worked like a charm!

    ReplyDelete

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

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

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

    ReplyDelete
  9. 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

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

    ReplyDelete