how to reset SQL Server sa password

Question

I've forgotten my sa password and already disabled windows authentication mode.
How can I reset SQL Server sa password with disabled windows authentication
tnx in advance

Answer 1

I had the same problem. Tried googling it and all the standard solutions didn't work so I had to get creative. Essentially my problem was that the SA account was disabled, also the system was set to only allow login by windows authentication, and finally the administrator account in windows also was not a member of the sysadmin role. So all the normal suggestions kept giving me permission denied errors (because I didn't have permissions to alter what other posts were telling me I should alter).

My solution is documented below, only apply the parts you need:

Stage 1: - Get SSMS working in single user mode

1. Open Sql server configuration manager (SSCM)
2. Click on SQL Server Services
3. Right click on SQL Server (MSSQLSERVER) and click properties
4. Click on the advanced tab
5. Find "Startup Parameters" and add the following
   • "-m;" at the beginning or ";-m" at the end (note the semi-colons but without quotes)
   • be careful to not add any white spaces as the parser is very picky
6. Press ok
7. Right click again and press restart
   • If you get any problems with the service not starting, verify step 5.

Stage 2: Part 1: - Enabling SQL Server and Windows Authentication mode

1. Start SSMS again.
   • This time when SSMS starts it will start in Single user mode which gives you full access.
2. Login with the usual windows account as before.
3. Right click on the server node (the very first / topmost node) and choose properties.
4. Click on Security
5. Under "Server authentication" click on "SQL Server and Windows Authentication mode" if its not already selected.
6. Click ok

Stage 2: Part 2: - Adding yourself into the sysadmin role

1. Click on "Security" and then "Server Roles"
2. Right click on "sysadmin" role
3. In the window that pops up, click add at the bottom.
4. In the window that pops up, click browse
5. Find your user and click the checkbox beside the name.
6. Click ok - this will return you to window in step 4 which will now have your user(s) in the lower box.
7. Click ok - this will return you to the window in step 3 which will add your user(s) to the role members list.
8. Click ok. - Congrats, you now have all the rights of the SA user.

Stage 2: Part 3: Altering the SA Password and enabling the account

1. Double Click on Security.
2. Double Click on Logins.
3. Right click on SA and choose properties.
4. Change and confirm the password fields (But don't press ok yet).
5. Click on the Status tab.
6. Click on Enabled.
7. Click ok

This is the process for resetting any users password (for a sysadmin). At this stage you should close SSMS.

Stage 3: Restarting SSMS in multi user mode.

1. Follow the steps taken in Stage 1 and simply remove the -m;

Stage 4: Trying on your bigger shoes.

At this stage you should be able to enable or disable the SA account from your login and change its password, also you should be able to enable or disable the ability to login with SQL Server authentication. Test these to ensure you have necessary rights.

Notes: Some taught should go into doing this. I feel that a sysadmin account should be the first thing setup once you setup a new SQL server instance.

Some people recommend removing the SA account (Personally, I recommend just putting a really strong password on it). As long as you have an account with sysadmin this is fine but someone must have an account with sysadmin access. This account should not be named Admin or some variant thereof like God for obvious reasons. Also avoid names such as your business name and combinations of either etc.

As regards to enabling or disabling sql server authentication that's a debate I'll leave for the DB admins.

Hope you find this helpful.