The problem is that you don’t know the password for the SQL Server SA account, and for whatever reason the Windows account you are using is being told you don’t have permissions to change the SA account properties. If you can't login to Management Studio using Windows Authentication this will not work for you.
First, you need to get the name of the SQL Server Service that is running, do this by clicking on Start and running services.msc
Scroll to SQL Server (name), there may be more than one, if so, get the information for all of them if you are not certain which is the instance for the account you need to reset. Double-click on the line and record the Service name
Open a command prompt and enter:
NET STOP [Service name]
NET START [Service name] /m
This will start the service in single user mode, which will allow you to make changes to the SA user account.
Start SQL Server Management Studio and login to the instance using Windows authentication. Go up to File and close the object explorer. Now click on New Query and enter this code:
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
ALTER LOGIN [sa] WITH PASSWORD=N’password’ MUST_CHANGE
In the code above where it says “password” you will enter your new password. You may have to change this again later so be prepared with two options for what you want your final password to be. Now execute the code. If there were no errors, exit from Management Studio and stop and restart the service:
NET STOP [Service name]
NET START [Service name]
Go back into Management Studio and login with the SA account using the new password you assigned. WARNING, you may get prompted to change the password, if so proceed with what will be the new final password.
One time when I did this I still had a problem because the SA account was not enabled. After doing the above, I had to create a new login that I gave sysadmin rights to that I then used to enable the SA account. I then deleted that temporary login. If I remember right, I did this while still in single user mode.
You may need to go back into Services and manually start dependent services that were shutdown when you first stopped the SQL Service.
Credits to the two sites that provided me with the help I needed to accomplish this:
Labels: password, recover, reset, sa, Server, sql