With SQL Server, when we forget or we lose the password of the DBA user 'sa', and we are the administrators of the database, we have a little problem. It's easy to forget this password because probably we have our own user administrator and don't use to log in with the sa account.
We also could have inherited the administration of a SQL Server database, with any documentation or information about the sa account, or any other domain account with administrator privileges. Even worse, we coul'd have installed an SQL Server database and next deleted the only login with admin privileges without knowing the password of the sa user.
For all this situations there is a better solution than reinstalling the database.
With SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016.., as a recovery plan for this 'disaster', we can start the SQLServer database in 'single-user' mode and access with any user of the administrator's group of the windows OS. The 'single-user' mode is intended to perform maintenance tasks, such as applying patches and other maintenance jobs.
This single user mode will allow us to connect using sqlcmd, for example, and add a database user to the SQL Server rol 'sysadmin'. After, with this user, we will have and admin user with privileges to change the password of the sa account.
Steps to recover the sa password on SQL Server
- Open the SqlServer Configuration Manager application. Search the SqlServer Service and look at 'Properties'. In the Advanced options or Init parameters add a -m at the end of the line. Press Accept and reboot the service.
(SQL Server Configuration Manager lived as an isolated application in SQL Server 2008.
In newer versions as SQL Server 2012 and systems as Windows 8 or Windows 10 this tool is an add in of the general 'Microsoft Management Console' (mmc.exe), and you will have to add this add in to use it.
Another option is to download an use the SQL Server Management Studio (SSMS), the administration tools of SQL Server 2016 that allows to administrate also instances from SQL Server 2008 and later)
- Once restarted, open the command line and the SQLCMD client and excecute this command lines:
sqlcmd -S localhost 1> EXEC sp_addsrvrolmember 'DOMINIO\Dba','sysadmin';
- Return to the SQL Server Configuration Manager (or Microsoft Management Console), remove the -m parameter added in previous steps and reboot the service.
Finally, connect to the database with the domain user modified in the command line (dba in this example), change the password of the user 'sa' and save it in a save place. That's all.
If we have applications that connect to our database and don't want that this applications enter in our database when we have activated the single-user mode, the -m parameter allows to identify what application is allowed to connect to the database in single user mode. We only have to specify the app name after -m.
For example, -m"SQLCMD" set the single-user mode for connections, and limits the connection to only the client software SQLCMD. If we prefer to work from the IDE and want to limit the connexion to only Management Studio, we should use -m"Microsoft SQL Server Management Studio - Query".
SQL Server Books
¿Do you want to learn more and go deeper into SQL Server database administration?
Check out this books about SQL Server.
