Maybe you have faced such an issue of losing SQL Server SA password. SQL Server SA password was ever set for protecting database, however, now it has become a problem for us to login SQL Server and access data in SQL Server. How to access SQL Server no logon password?
If you are a computer novice, the easiest way is undoubtedly easy-to-use and safe password recovery tool. SQL Password Genius is one of such effective recovery tools for SQL Server 2000/2005/2008/2012/2014. Now please see how does it help to access SQL Server when you don’t have SA password?
First Way: Access SQL Server by Recovery Tool without SA Password
Step 1: Download and install SQL Password Genius on computer where you run SQL Server.
Step 2: Run SQL Password Genius and import SQL master database.
Tips: Please stop SQL Server Service in SQL configuration tool before running SQL password recovery tool. Otherwise, reset lost SQL sa password would be not so easy, perhaps failed at last.
Click Open File button on program to browse master file in computer and open it. Master file route would be shown in a file box, and all the users available for this database are listed, see the picture below.
Step 3: Select SQL Server SA account and reset password.
Generally, SQL Server SA account is in the first place of user box. User Name and Password status would let us know whether the user’s password should be reset. If its password status is “Unknown” , select “sa” account and click Reset button.
Instantly, a dialog pops up and asks us to set a new password for it. Type a new password and click OK. Then the new password for sa account would appear in user box.
Now, exit SQL Password Genius, restart SQL Server Services in configuration tool, you will be able to start SQL Server and access it with sa account and its new password.
Second Way: Access SQL Server in Single User Mode
If you have a member there is a member of the Windows admin group, this way also would help you to access SQL Server with Windows credentials. It can be used for SQL Server 2005/2008(R2).
To start SQL Server in single user mode is very simple like the steps below (take SQL Server 2008 as example:
Step 1: Go to SQL Server Configuration Manager and click on SQL Server 2008 Services.
Step 2: Click on desired SQL Server instance and right click go to properties.
Step 3: On the Advanced table enter param ‘-m;‘ before existing params in Startup Parameters box.
Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. You would see a message “SQL Server started in single-user mode.”
Once this is done, now you will be only able to connect SQL Server using sqlcmd utility with Windows authentication.
Tips: In the whole process, probably you have found there is so much downtime. If you want to make it faster, download PSExec.
How to set SQL Server access with single user mode
However, this way is disaster recovery, so only had better be used when no other recovery method is available. It allows Windows administrator override within SQL Server. And it requires following intrusive actions during the process:
- Stop SQL Server and restart it in single-user mode
- Connect to SQL Server using Windows credentials
Related Articles: