In this article, we will consider the procedure on how to reset SA password in SQL Server 2017. In the event that the SA password was successfully forgotten or there are no users with administrative rights to MS SQL 2017. We will show how to reset the password of the SA account, and, if necessary, provide the right Windows account with MSSQL administrator rights.
The situation when the user SA is lost / forgotten by the MS SQL database happens quite often. As a rule, the option of reinstalling SQL and reconnecting the database is not considered; In this case, the configuration parameters stored in the system DB master will be lost.
SQL Server provides much more convenient access recovery functionality. The fact is that members of the local group of system administrators on which SQL Server is installed have full authority over the DBMS running in single-user mode. By running SQL Server in single user mode, you can reset SA password in SQL Server or grant administrative rights to the desired Windows write.
Council: Recall that starting with SQL Server 2017, the local group of system administrators on which SQL Server is installed was not added to the sysadmin role on the SQL server by default.
The procedure for starting SQL Server 2017 in single-user mode and restoring access is as follows (the main condition is that you must have administrator rights on the system on which MSSQL is installed):
We stop the service of the necessary instance of SQL Server (in our case, the name of the MSSQL instance is DBSERVER ):
net stop MSSQL$DBSERVER
Open the Registry Editor (regedit.exe) and go to the section of the SQL service startup
Changing the value ImagePath , adding as an argument sqlservr.exe key « -m ».
In our example, this value is:
C:\Program Files\Microsoft SQL Server\MSSQL14.DBSERVER\MSSQL\Binn\sqlservr.exe
-m -s sEPOSERVERMSSQL ImagePath – SQL exclusive mode
Run the SQL Server service: net start MSSQL$DBSERVER
Now SQL is started in single-user mode and allows any member of the local computer administrators group to connect to an instance of SQL Server with sysadmin rights.
Using Management Studio, you can reset SA password or provide the necessary Windows account with the sysadmin role (This operation can be performed from the sqlcmd command line:
EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';)
It remains to stop the SQL service again, change the value of the ImagePart key to the original one by removing the “-m” attribute (
"C: \ Program Files \ Microsoft SQL Server \ MSSQLDBSERVER \ MSSQL \ Binn \ sqlservr.exe" -s sDBSERVER"
) and run SQL Server in normal mode
Council: At the time of this writing, I found that SQL Server 2017 can be run in single-user mode and from SQL Server Configuration Manager.
To do this, add the argument ” -m ” in the properties of the required instance in the Advanced tab of the Startup Parameters. In the rest – the whole procedure is similar.
There might be chances that, after trying the above manual approach user still failed to reset the password. Hence we suggest you another reliable solution to reset SA password in SQL Server
Note: Software prompt for backup your master.mdf file before going to reset SA password in SQL Server
It is perfectly normal to forget the password from time to time, and this does not mean that there will even be minor inconveniences. The recovery measures described by me will allow you to reset SA password in SQL Server 2017 & below versions without downtime and password decryption.