How to recover your sql server if you can not login

Friday, May 30, 2008
by asalvo

Don't ask

  • Shutdown your SQL Server service, from the services MMC
  • Stop all services and applications from connecting to your SQL server. If they are connecting via the network, you could use the windows firewall to restrict access. The reason for this, is once you are in single user mode, another application may connect and consume the one connection.
  • Open a command prompt window and navigate to the location of SqlServr, which should be %Program Files%Microsoft SQL ServerMSSQL.1MSSQLBinn.
  • Type sqlservr -m to start SQL server in single user mode
  • Open Sql Server Management Studio (SSMS) and connect to your SQL server
  • Fix your security
  • Go back to the command prompt window and press ctrl+c, and then Y to stop SQL server
  • Restart SQL server, and any dependent services (like SQL agent) in the services MMC.

If you have UAC enabled (Vista or Win2k8) you have to run the command prompt, and SSMS as an administrator.


Question…does it really matter if you deny local computer and domain administrators access to the SQL server, if any local administrator can restart SQL server in single user mode? The reason I ask, is there are a lot of “Best Practices” that say you should deny access to normal computer administrators. I’ve even heard that in some industries and sectors such as legal, healthcare, etc, you have to restrict who has access to the data. Maybe it’s just a you need to make a good effort.


comments powered by Disqus