SQL Server Single-User Mode
Sometimes you can’t get into SQL Server. Maybe you forgot a password, or something got misconfigured. You are locked out.
What to do when this happens? My go-to is putting SQL Server in Single-User Mode.
Doing this is pretty straightforward. First, open a command shell in administrator mode. Then stop the SQL Server Service.
net stop MSSQLServer
Then I start SQL Server in Single-User Mode.
net start MSSQLServer /mSQLCMD
Now I can use SQLCMD to make a quick change to the database. Such as changing the sa password. Running SQLCMD is as easy as typing SQLCMD at the existing command prompt.
ALTER LOGIN sa WITH PASSWORD = 'PASSWORD GOES HERE';
You might need to enable the sa login too.
ALTER LOGIN sa ENABLE;
Once this is done, you can exit SQLCMD.
Exit
Now that you have an sa account password, you need to restart SQL Server, and that password will work.
net stop MSSQLServer net start MSSQLServer
Once you have completed this, you can log in to the SQL using the sa password. This account should enable you to fix whatever account problems you are having. Once you get your SQL Server running again, you should consider disabling the sa account again. The sa account is often a hacker’s first attempt against your database. A straightforward security tactic is just to disable that account.
ALTER LOGIN sa DISABLE;
