Grant sysadmin permissions to windows login when SQL Server SA login password is not known

Here are the detailed steps:

1: Start SQL Server in Single User Mode Open SQL Server Configuration Manager.

 
Stop the SQL Server Instance you need to recover

                                      |  
Right-click on the SQL Server Instance and select Properties.

                                      |  
Click on the Advanced tab, and add -m or -f; to the beginning of Startup parameters.

                                      |  
Click OK and start the instance.

2: Add an existing login or a newly created one to the sysadmin server role

After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login or a newly created one to the sysadmin server role.


Open an elevated command prompt and enter the command:

SQLCMD -S myServer\instanceName

Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.
At the next prompts, enter the following commands to create windows login for sysadmin role:

CREATE LOGIN [US\TEST] FROM WINDOWS
go
ALTER SERVER ROLE sysadmin ADD MEMBER [US\TEST] 

(Grant sysadmin role for newly created login or existing login)

go
quit

 
Stop the SQL Server instance.

                          |  
Remove the -m option from the Start parameters field, 

                          |

and then start the SQL Server service.


At this point you should be able to login to SQL Server with sysadmin privileges and reset the SA login password.