Accessing Sql Server 2019 when logins disappear after installation

Accessing Sql Server 2019 when logins disappear after installation

Problem

On numerous occasions after completing a SQL Server installation (2016,to 2019) I’ve noticed that the SA user account and the other specified sysadmin accounts all disappear from the server.  This post will show you how to access the database again to re-add those users. It is important to note that this method will require downtime, so if it’s a case where your login is the only sysadmin account on the database and you’ve forgotten you password, to reset it the database will require a restart.

Solution

  1. Log on to the server that host the database.
  2. Go to the start menu, locate and  run Sql Server Configuration Manager as administrator (right clicking, Select More > Run as Administrator)
  3. The Sql Server Configuration Manager Appears.
  4. In the left panel select SQL Server Services
  5. In the right panel right-click the instance (SQL SERVER (MSSQLSERVER) and select properties
  6. In the new window that appears select the startup parameters tab
  7. In the specify a startup parameter box enter -m and press add. -m puts the database in single user mode
  8. Click apply (you will be prompted to restart the instance)
  9. Click ok
  10. Back in the Sql Server Configuration Manager window right click the instance again and hit restart.
  11. Open the command prompt in administrator mode by going to the start menu, type in cmd, right click , more > run as administrator.
  12. In the new window that appears (cmd window) enter: sqlcmd -E
  13. You now have access to the database using the local administrator account on the server
  14. To reset the sa password or any other sysadmin password enter: ALTER LOGIN [sa] WITH PASSWORD=N'Password' GO
  15. You can now close the command prompt
  16. In step 7 we placed the database in single-user mode before we can access the database we will need to take it out of this mode.
  17. Repeat steps 2 to 10, but for step 7 we are going to remove instead of adding the parameter by selecting -m and hitting the remove button.
  18. Open management studio and enter your new credentials
  19. Please see the gifs below for a working example

How To GIFs Accessing Sql Server 2019 when logins disappear after installation

Accessing Sql Server 2019 when logins disappear after installation Removing -m from start up parameter in sql server

Conclusion

This is only one of many solutions that a DBA can use to access a database that they are locked out of(Other solution: How to easily access SQL Server 2019 if locked out). It is important to note that there will be down time with this method, and DBAs must take that into consideration before attempting it. In another post we will cover how to access the database without incurring any data loss or down time. However this method is perfect for a newly installed instance where the sysadmin accounts have disappeared or the sysadmin password is forgotten. Check out our other articles How to add a new Named Instance in SQL Server and 5 free tips on how to keep SQL Server Database performing