In this article, we will take a look at how to reset SA (system administrator) password in Microsoft SQL Server 2019/2017/2014/2012 . This guide will help you restore access to the MSSQL database if you cannot authenticate using the sa account (the sa password was forgotten/lost/the account was disabled) or as a Windows user (if there are no users with MS SQL sysadmin permissions).

To restore access to the database without completely reinstalling the MS SQL instance (and with the loss of the master database), you need to start SQL Server in a single-user mode. In this mode, you can log in to SQL Server under any account with local administrator permissions on the computer on which MS SQL Server is installed. You can then reset the sa account password and/or grant administrative permissions to specific Windows accounts.

In order to run MS SQL Server in single-user mode, sign in to the MSSQL host under any account with local admin permission in Windows.

1. Stop the SQL Server instance (in our example, the MSSQL instance name is MSSQLSERVER) from the command prompt net stop MSSQLSERVER or using PowerShell: Get-Service MSSQLSERVER| Stop-Service
2. Open the registry editor (regedit.exe) and go to the SQL service registry key: HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQLSERVER;
3. Change the value of the ImagePath parameter by adding the -m option as an argument to sqlservr.exe. For example, set the following startup string: "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER -m
4. Start the SQL Server: net start MSSQLSERVER
5. Your SQL Server is now running in single user mode and allows any member of the local Administrators group (or the built-in Windows administrator) to connect to the SQL Server instance with sysadmin privileges;
6. Open the SQL Server Management Studio (SSMS) and connect to the SQL Server Database Engine using an account that is a member of the local Administrators group;
7. Go to Security -> Logins and find the sa user. The sa user is disabled by default;
8. Open its properties, go to the General tab, and set a new SA password. Then enable this account on the Status tab (Login -> Enabled);
9. In order to allow connecting to MSSQL under local SQL accounts (including sa), you need to enable the SQL Server and Windows Authentication mode in the server properties on the Security tab. Otherwise, when connecting under the local sa account, an error will appear:

   Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

   

10. Also in the Logins section, you can add the required Windows users (local or domain) and assign him a Sysadmin tole (on the Server roles tab). Or, you can add role to user from sqlcmd cli:
    EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';)
GO
    
11. It remains to stop the MSSQL service again, return the initial value of the ImagePart registry parameter by removing the “-m” switch (C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER);
12. Start the SQL Server service normally;
13. You can now connect to the SQL server under the sa account in the SQL Server Authentication mode or under the Windows user account that you have granted sysadmin privileges in SQL Server.

Also, you can use the sqlcmd command line tool to reset the SA password and grant access to SQL Server:

1. Start SQL Server in single user mode as described above;
2. Open command prompt and connect to your SQL Server instance: sqlcmd –S localhost
3. To change the SA user password and enable it, run the following T-SQL commands:
   ALTER LOGIN sa enable
GO
ALTER LOGIN sa WITH PASSWORD = 'new_sa_pass331'
GO
4. To grant sysadmin permissions in MSSQL for a Windows account (local user in this example), use the commands:
   CREATE LOGIN [mun-sql01\jsmith] FROM WINDOWS
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [mun-sql01\jsmith]
GO

In this article, we have shown how to restore access to SQL Server in single-user mode. In this mode, you can reset the sa (and other SQL users) password, or grant SQL admin permissions to Windows user accounts. Single-user mode can also be used to restore the temdb database in MSSQL Server.