There comes a time in every DBA's life where they mislay the sa password, or discover a SQL Server somewhere that doesn't have the AD sysadmin group added and no one can remember the sa password.
As long as you have administrator access to the server, getting back access is relatively easy in concept, but can be a bit rough in practice.
The general principal is to:
- restart SQL Server in single user mode
- connect to the SQL Server
- either reset the sa password or add in the sysadmin group
- restart SQL Server in multi-user mode
I prefer to do all of this from a command prompt, here's what I do.
- Logged into the host Windows server as a local administrator, start a command prompt as an administrator, i.e. using the Run as administrator option
- Stop the SQL Server Agent service
net stop SQLSERVERAGENT - Stop the SQLServer service
net stop MSSQLSERVER - Here's the trick, start the service in single user mode (/m) and only allow connections from sqlcmd. Also start sqlcmd in the same command. This stops anything else grabbing the connection before you can get to it.
net start MSSQLSERVER /m"SQLCMD" && SQLCMD - Now the password reset/group add can be carried out, remembering that sqlcmd needs semi-colons and a GO statement to execute.
- Once the account reset has been done, restart the service in normal mode and start the SQL Agent service.
net stop MSSQLSERVER && net start MSSQLSERVER
net start SQLSERVERAGENT
You should now be able to connect as normal
No comments:
Post a Comment