Regaining Access to an SQL Instance

After a previous employee left an organisation, no one had access to an SQL Instance and the SA Password was unknown.  In this article I will show you how to regain access to an SQL Instance.

This process was performed in SQL Server 2012 Enterprise Edition by booting the SQL server into Single User Mode.

First stop the SQL service for which we need to recover the password.

Then start the service by entering a Start parameter of "-m" in the services window in Control Panel.

Next connect to the instance with SQLCMD.exe -S "SERVERNAME\Instance".

To grant sysadmin to a user or entire group such as Domain Admins, run the following command:

EXEC sp_addsrvrolemember 'DOMAIN\Domain Admins', 'sysadmin'; 

GO

Next run "quit" to close SQLCMD.

After this, remove the -m from the SQL Instance and start the instance normally.  Now anyone in the Domain Admins group will have sysadmin rights to the instance.  Login with a Domain Admin account and reset the SA password (provided your Instance is setup for Mixed authentication).

Enter the new password and click OK.