Have you or your coworker ever forgotten your sa password for your SQL Server and you can no longer log into your SQL server? Strangely, it had happened to me twice in last few months.
If you find yourself in the same situation, read this before you start screaming.
When we lose access to SQL server, it’s possible to connect to SQL Server using your Windows local admin. The trick is to run SQL Server engine under single user mode.
Now let’s take a look at how we can get our server back.
Before we begin, we need to have a user who is in local admin group. For demonstration purposes, I created SQLUser. Notice, SQLUser is a Windows user, it doesn’t have a SQL Server login.
After we log in as SQLUser, first thing we do is to stop SQL server engine:
Net Now we need to start SQL Server engine in single user mode. We could do it with /m parameter:
OK, now we are ready to connect to SQL Server, Use SQLCMD to login:
Perfect! We are in. Now we can use ALTER USER <USERID> WITH PASSWORD = <NEWPASSWORD> to change sa or any login password.
Welcome back, my server!