Our SQL Server Administrator has left with the SA password. What can we do?

You have inherited a SQL Server 2012 system for which you don't know (or it doesn't exist) the sa password and don't have a Windows account with sysadmin privileges.  Or perhaps you have forgotten the SA password.  What on earth can you do?  All you have is the dbo (database owner) privileges.

You could try

• Restoring Master database from backup.  However you won't succeed without the sa password.
• Restore the Master database.  However, you will lose all server level configurations including logins, permissions, linked servers, replication, legacy etc.
• Re-installing SQL Server from scratch.  However you will also lose all the server level configurations.

Here is a "backdoor" to SQL Server 2012, which can help you gain sysadmin access to SQL Server, so long as you are a member of the local Windows administrators group on the SQL Server machine.

SQL Server 2012 allows members of Local Administrators group to connect to SQL Server with SysAdmin privileges.

To create a new SYSADMIN account

1. Start a command prompt using a Windows administrator account.  Navigate to the SQL Server 2012 binn folder (or have this folder in your environment path).  It is commonly found in c:\Program files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
2. From the command prompt type "SQLServr.exe -m" or "SQLServr.exe -f", which will start SQL Server.  Do not close the command prompt.
3. Open up another command prompt and use the SQLCMD command to connect to a server and instance. 
SQLCMD -S EasternMining\SQL2012

4. You are now logged onto SQL Server from the command prompt.  You can now create a new account and granting it any permission. The following command will create the user RichardAdmin
CREATE LOGIN RichardAdmin WITH PASSWORD='AV3ry53curep@ssw0rd'
GO


5. Now add the user to SysAdmin
SP_ADDSRVROLEMEMBER RichardAdmin, SYSADMIN
GO

6. You have now added the new user and granted it SYSADMIN privileges.  Now, all you need to do is stop SQL Server and start in normal mode.  
7. From SQL Management Studio you will be able to login using the new account and grant SYSADMIN privileges to other accounts.
8. Don't forget the sa password or ensure there is a Windows account that has SYSADMIN privileges.