The scenario of a missing SA password:

You are the senior DBA of your organization, responsible for all the databases that hold the critical info. In order to secure
the environment and the databases, you performed the next recommended steps:

1) Remove all built-in admin account from SQL Server logins.

2) Remove all users from SYSADMIN server role except SA (assuming you are
working in a mixed mode environment).

3) Change the SA account password to something extremely complex.

4) For SQL daily operations, you use a domain user account with DBO permissions on
user databases with no SYSADMIN privileges on the system.

5) You haven't saved the SA password.

Since the SA password is complicated, you are using your domain account and in time you have forgotten the SA password.

Well, you are the only one on the team that knows the password but you don't remember it.

The first option is to write the resignation letter.

The recommended option is to continue reading.

Here are the Steps to take back control of your SQL 2012 SA:

Start the damaged instance of SQL 2012 in single user mode from command prompt with Administrator rights and type:
SQLServr.Exe –m (or SQLServr.exe –f).
The SQLServr.exe will be located in the Binn folder, make shure that the Binn path exist in the environmental parameters:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>.

Now that SQL Server 2012 was started in single user mode, we open up another CMD (with elevated right) window and f
rom the command prompt connect to SQL Server 2012 instance with the SQLCMD command:

SQLCMD –S <My_Machin\My_Instance>

For Example:
SQLCMD –S ServSQL2012\Inst2012

When connecting in –S mode you are to the SQL in an windows authentication mode and with the user admin right.
Once connected to the SQL Instance we can start and create a new login "NEW_SA"
The command to create the new login:

1> CREATE LOGIN <Login_Name> with PASSWORD=’<Password>’
2> GO

 

Once the login was created we will add it to the SYSADMIN role.

The command to add a login to the SYSADMIN role:

1> SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
2> GO

Now that the new LOGIN was created and that the added to the SYSADMIN role we can log with it to the
instance and change the SA password to a new one.

Comments

• Anonymous
  July 23, 2013
  Alternatively, if you don't want to/can't bring your server down, use this technique: sqlblog.com/.../leveraging-service-sids-to-logon-to-sql-server-2012-instances-with-sysadmin-privileges.aspx