Lost SA Password for SQL Server

YOUR SA PASSWORD FOR SQL SERVER 2012 IS LOST. NOW WHAT?

Here’s the scenario:

You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:

þ  You have removed any and all built-in administrators account from SQL Server logins.

þ  You have removed all users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)

þ  You have set the password of SA account to something extremely complex which would be hard for anyone to guess or remember.

þ  For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on user databases but doesn’t have SYSADMIN privileges on the system.

þ  You have not documented the SA password anywhere to prevent others from knowing the SA password. (Also, it’s not a good practice to document the password)

Since you have set the SA password to be complex and you have been using your domain account and not the SA account for all your daily database related activity on SQL Server, the unthinkable has happened “you forgot your SQL Server’s SA password”. You are the only person who knew the SA password in your team and now you don’t remember what it was and you need to make some server level configuration changes to your production SQL Server boxes.

What would you do now? Here are some of the options I think you may do:

1.       You will try logging in as SA with all possible passwords you have in your mind.

2.       You will look for the SA password on your computer hard-drive or in your emails (If you had stored it in some file which is a bad practice)

3.       Try to restore MASTER database from database backup. However, this will not help because you will run in to the same issue as you don’t remember the SA password.

4.       Rebuild Master database. This may not help as you will lose all system/server level configurations and settings including logins, permissions and any server level objects.

5.       Re-install SQL Server 2012 and attach all user databases. This may not work as you may experience same issues that you would experience with #4.

All your attempts to login in to the system using SA password have failed and now it is time for you to call up the world’s best product support services team “Microsoft PSS”

Here’s what you can do:

Many folks have asked me about this and today, I’d like to share with you a backdoor to SQL Server 2012 which would help you gain SYSADMIN access to your production SQL Servers. However, that would mean your Windows account will need to be a member of the local administrators group on Windows Servers where SQL Server 2012 Services are running.

SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.

Steps to take control of your SQL Server 2012 as an SA:

1.       Start the SQL Server 2012 instance using single user mode from command prompt by launching  the command prompt as an administrator. (Note: You can also start SQL Server 2012 using minimal configuration which will also put SQL Server in single user mode)

2.       From the command prompt (Run as Administrator) type: SQLServr.Exe –m (or SQLServr.exe –f) and let the SQL Server 2012 database engine start. Make sure you do not close this command prompt window.

You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have SQL Server 2012 Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server 2012 machine. Usually the Binn folder is located @ C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>

3.       Once SQL Server 2012 service has been started in single user mode or with minimal configuration, you can now open up another command line window as an administrator and use SQLCMD command from command prompt to connect to SQL Server 2012 instance:

SQLCMD –S

Example: SQLCMD –S “SALEEMHAKANI”

You will now be logged in to SQL Server. Keep in mind that you are now logged in as an Admin on SALEEMHAKANI SQL Server 2012 instance.

4.       Once you are logged into the SQL Server 2012 using SQLCMD from the command prompt, you have the option of creating a new account and granting it any server level permission.

Let’s create a new login in SQL Server 2012 called “Saleem_SQL” and then add this account to SysAdmin server role.

      To create a new login from the command prompt after performing Step 3:

1> CREATE LOGIN ‘’ with PASSWORD=’’

           2> GO

Example:

1>     CREATE LOGIN SQL_SALEEM WITH PASSWORD=’$@L649$@m’

           2>     GO

To Once the new login “SQL_SALEEM” has been created, now let’s add this login to System Admin server role on SQL Server 2012 instance.

To add an existing login to SYSADMIN server role, execute the following:

1>     SP_ADDSRVROLEMEMBER ‘’,’SYSADMIN’

           2>     GO

Example:

1>     SP_ADDSRVROLEMEMBER SQL_SALEEM,’SYSADMIN’

           2>     GO

 The above operation will take care of granting SYSADMIN privileges to “SQL_SALEEM” login.

5.       Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

6.       Log in to the SQL Server 2012 management studio or from the command prompt using “SQL_SALEEM” account and it’s respective password, you now have system admin access to your SQL Server 2012 instance. You may now reset the SA password and take control of your production SQL Server boxes.

Reference : Saleem Hakani