Option 1 – Using Windows built-in Administrator Account
If you have installed the SQL Server with the Windows Administrator in a sysadmin role, you can log in to the server then into the SSMS with that account. After you log in, in the Object Explorer go to Security > Logins > Double Click on SA and change the password in the dialog box. Then you should be able to log in with the new password.
Keep in mind that you can also use any account with a sysadmin role to change the SA password. Suppose you have an account called SYSDBA, or a SQLADMIN. If you know their password and they belong to sysadmin role, you’ll be able to use them to change the SA password.
Option 2 – Use Command Line to Add Windows Administrator account
Before I go into the details of this method, you need to be aware that this will require a SQL Service restart. Only perform this method if you are able to restart the service and can afford the downtime.
Another thing to note is that this method works with SQL Server 2005 and up. It applies to users that are part of Windows Administrators group and as such, your account that you add should belong to this group.
Prior to connecting to SQL Server via command line, we want to make sure the service starts in a single-user mode. To do this, we need to add a “-m” startup parameter to the SQL Service startup settings.
On the actual SQL Server machine, Go to your SQL Server 20xx Configuration Manager then go to SQL Server Services and double-click on the SQL Server instance where you need to change the SA password. Select the Startup Parameters tab, type in –m in the first line and click Add:
When you click OK, the prompt will tell you that the changes will require a restart, so go ahead and restart your SQL Server Instance.
Next, we need to fire up Windows Command Prompt and connect to your SQL Server Instance. With the –S Switch, we can specify the server and instance name. If your instance is default, you don’t need the –S Switch. I’m using it because I have to connect to an instance called ‘VEDRANSQL’. The other switch –E, is telling the SQL command to connect using trusted connection from a local Administrator account. If successful, you should see the 1> prompt where you can type in your first line of T-SQL Code.
We’re going to type the following T-SQL:
EXEC sp_addsrvrolemember ‘DOMAIN\username’, ‘sysadmin’; GO
Obviously, replace the DOMAIN\Username with your actual admin account. After first line, when you press Enter, we also need the GO keyword in line #2. This will act like the Execute or F5 button in SSMS. Commands should look like this:
At this point, we want to undo the parameter change we did in the previous step. Remove the –m from the parameter list and restart the SQL Service.
Now, you should be able to log in with your Windows Administrator account!