<

How to Reset a Lost SA Password in Microsoft SQL Server

Published on
5,858 Points
1,958 Views
4 Endorsements
Last Modified:
Joseph Hornsey
I built my first network in 1994 using PowerLAN.  From there I messed around with NetWare and then moved to Windows NT and Cisco.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It's happened to all of us.  Someone installs a SQL server and then promptly forgets the sa password without documenting it.  Normally, it's not that big of a deal - just log in with a different account, right?  Oh, wait... there aren't any.

Recently, I ran into a scenario where a corrupt Active Directory had been rebuilt, however the SQL server logins got lost in the shuffle.  Consequently, there was no way to log into the SQL server except for the sa account, and that password was long-forgotten.  Fortunately, there's a way to fix this problem without reinstalling SQL and reattaching the databases.  

Microsoft SQL Server has the ability to launch in Single-User Mode.  In this mode, any account that is a member of the local Administrators group will be able to log in to the server with sysadmin privileges.  To launch in Single-User Mode, one must use a startup parameter for the SQL instance in question.

While in Single User Mode, only one user can be connected at a time (as the name would imply).  You'll connect and interact with the SQL instance from a command prompt using SQLCMD commands.

1. Open an elevated Command Prompt.

Admin-Cmd-Prompt.png2. Stop the SQL Instance.  The default is MSSQLSERVER.
net stop MSSQLSERVER

Open in new window



net-stop-mssqlserver.png3. Start the SQL Instance using the '/m' switch and specifying you'll use SQLCMD to interact with the instance.  The input following the '/m' switch is case-sensitive.  There's no indication you're connected in Single-User Mode, so don't worry if you don't see anything.
net start MSSQLSERVER /m"SQLCMD"

Open in new window


net-start-mssqlserver-with-m-sqlcmd.png

4. Connect to the instance with SQLCMD.  Just type 'sqlcmd' and hit <ENTER>.  You'll find yourself at a numbered prompt.  This means you're connected to the default instance.  If you want to specify a particular SQL instance, just use the appropriate SQLCMD switches.  The syntax will be:

sqlcmd -SServerName\InstanceName

Open in new window


sqlcmd-prompt-1.png

5. From here, you use Transact-SQL (T-SQL) commands to create a login.  I'm going to create a login called "RecoveryAcct" and give it the password "TempPass!".  Since you're issuing T-SQL commands directly, you'll need to use the 'GO' command, too.  There's no indication the command was successful; you just end up back at a '1>' prompt.  If you don't get an error, you can assume all is well.

CREATE LOGIN RecoveryAcct WITH PASSWORD=’TempPass!’
GO

Open in new window


sqlcmd-creat-login.png

6.  Now, use more T-SQL commands to add the user to the SysAdmin role.  Again, you'll need to use the 'GO' command and if you don't get an error, you can assume all is well.

SP_ADDSRVROLEMEMBER RecoveryAcct,’SYSADMIN’
GO

Open in new window


sqlcmd-add-role.png7. To exit SQLCMD, type 'exit' and hit <ENTER>.  Next, stop the SQL instance and then start it again without the '/m' switch so it is no longer in Single-User Mode.
net stop MSSQLSERVER && net start MSSQLSERVER

Open in new window


net-stop-and-net-start-mssqlserver.png8. Launch SQL Management Studio using SQL Authentication and log on as the user you just created.

sqlmgmtstud-logon-with-recovery.png9.  Now, you can look at your security settings and make the appropriate changes you need.  You'll see your recovery account listed.

sqlmgmtstud-user-tree.png
You can also use the SQL Server Management Studio if you're not comfortable with SQLCMD.  When you are starting the instance in Single-User Mode, you can always specify "Microsoft SQL Server Management Studio" instead of "SQLCMD" after the '/m' switch in step 3.
net start MSSQLSERVER /m"Microsoft SQL Server Management Studio"
GO

Open in new window


I hope this helps you out.  If so, please click on the blue and white "Good Article" button below.
 
4
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 66

Expert Comment

by:Jim Horn
Well done.  Voting Yes.
0
 
LVL 51

Expert Comment

by:Mark Wills
Good Article and very well presented. Gets my YES vote :)
0
 

Expert Comment

by:Abrienne Jonethan
Very Good and Informative Article,
I have also gone through other blog as there are other ways to reset a Lost SA Password in Microsoft SQL Server.
Apart from recovering from manual way you can also use free software like SQL Password Recovery
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month