It can happen that you lose all passwords or that you delete the users in a DB, so you have no access or limited access to the database instance.
There are many articles on the Internet that talk about SQL password crackers or other strange methods (some cost you money) to sort this out, but there is a much better and simpler method.
For this to work, you need access to the server console (for example via RDP). The trick is to activate and reset the password for the sa account, then login with sa and create (or reconfigure) the users you need. Here are the steps in detail:
- If you don't run SQL in "Mixed Authentication Mode", change the following Registry key to "2":
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQLServer\LoginMode=2
- Stop the service "SQL Server (SQLEXPRESS)"
- Open cmd as Administrator and type:
NET START MSSQL$SQLEXPRESS /f /T3608
- Start SQLCMD:
SQLCMD -S .\SQLEXPRESS
- Overwrite the password of the sa account, the password below is just an example, use your own, complex password:
ALTER LOGIN sa WITH PASSWORD = 'SuperSecretPa55word';
- Enable the sa account (it is normally disabled):
ALTER LOGIN sa ENABLE;
- Exit the SQLCMD:
- Stop the SQL service:
NET STOP MSSQL$SQLEXPRESS
- Start the SQL service normally
- Login to the DB instance with SQL Management Studio
- Under Security > Logins create/modify users as needed. Make sure another user besides sa has "sysadmin" rights, so you can disable sa again for security reasons
That's it, no extra tools needed.