If you happen to forget your sa password, you can still recover it as long as you have access to the server. Here are the steps to do it.
Launch Sql Configuration Manager under Configuration Tools folder.
Look for your SQL Server instance (the default is MSSQLSERVER) and stop the service. You can click the stop button while having the SQL Server (MSSQLSERVER) row highlighted or you can right-click on it and select Stop.
Launch the Command Prompt.
Next, we want to run the SQL Server in a single-user mode by adding “/m” parameter with the client application name:
net start MSSQLSERVER /m"SQLCMD"
Then we need to connect to the database on the machine using a trusted connection:
sqlcmd -E -S localhost
If you’re connecting to a database on a local machine, you can substitute “localhost” with a “.” (dot), which makes it look like so:
sqlcmd -E -S . or sqlcmd -E -S.
The two are identical except the former is easier to read. Another note is if you’re using SQL Server Express, you need to add “\SQLEXPRESS” after the period. You can see the difference on the example below.
For SQL Server 2008 or Older
CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1' GO sp_addsrvrolemember 'tempUser', 'sysadmin' GO
For SQL Server 2012 or Later
CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1' GO ALTER SERVER ROLE sysadmin ADD MEMBER tempUser GO
You can type “exit” to quit SQLCMD.
Restart the Sql Server service to get out of the single-user mode:
net stop MSSQLSERVERfollowed by
net start MSSQLSERVER
Launch SQL Server Management Studio and connect to the local database using the new login you just created.
Expand on Security, then expand on Logins.
Right-click on user sa and select Properties. Enter the new password and click OK. And you’re done.
Now you can login to the database using the sa login and the new password you set. For security purpose, make sure you delete the tempUser afterwards.