If for any reason, you forget the password of the administrator user (sa) and don't have Windows authentication enabled, you can recover it very simply in SQL Server. You only need to start the SQL Server instance in "single-user mode".
This mode is useful in situations where you need to perform maintenance tasks, troubleshoot issues, or make changes to the SQL Server configuration, and you want to ensure that only one user has access to the SQL Server instance while these actions are being performed.
Therefore, first stop the SQL Server by using CMD or the SQL configuration console:
# NET STOP MSSQLSERVER
Then, start it again, but with the "Single-user" option:
# NET START MSSQLSERVER /m
You can also add the option from the console in "Startup Parameters" in the SQL Server properties:
Once it's started, use SQLCMD to connect to it:
# SQLCMD
This will allow you, for example, to add a new user:
# CREATE LOGIN new_user WITH PASSWORD='New_Secure_Password'
GO
And grant them SYSADMIN permissions:
# EXEC sp_addsrvrolemember 'new_user', 'SYSADMIN';
GO
EXIT
Then, stop the SQL Server again using CMD or the configuration console:
# NET STOP MSSQLSERVER
And then start it without the startup parameter:
# NET START MSSQLSERVER
Once SQL Server is started normally, you can log in with the new SQL user with SYSADMIN permissions to make the desired changes.
We hope this tutorial has been helpful. If you have any questions about this or any other issues related to your servers on Clouding, don't hesitate to contact support@clouding.io. We're here to help!