Problem
You need to use SQL management or other tools and need to sign in but you have no access to the SA password nor any other sysadmin accounts (if they exist)
Solution
– Open SQL Server Configuration Manager
– Stop the SQL Server Instance you need to recover the SA password if there are multiple instances
– Run a command prompt as administrator with the following command
c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -c -f -m
– Open a second command prompt
– Run sqlcmd and press enter
– Run a Transact-SQL command below to add an existing account or a new account to the sysadmin server role.
EXEC sp_addsrvrolemember ‘DOMAIN\Username’, ‘sysadmin’;
GO
*Replace “domain\username” with your domain and required user name
– Press CTRL + C in the sql server process window (first command prompt)
– Answer Y to shut down the service
– Start the SQL Service Instance from the SQL Server Configuration Manager
– Open Microsoft SQL Server Management Studio and login with the account you added
– Under the DB, expand Security, then Logins
– Open the properties for the sa account, and reset the password
Tested Platforms
Windows 2012 R2
SQL Server 2012
Hits: 154