If something unimaginable happens and you forget your SQL Server password, there are ways to restore access to the system.

You are a respected and trusted database administrator in your organization. You maintain and update several important services performed by SQL Server in your production environment. You have taken the following steps (reflecting the recommendations to be followed by any conscientious database administrator) to protect your company’s SQL Server databases from any kind of unauthorized access.

• Delete all built-in SQL Server administrative accounts.
• Remove all users (except System Administrator or SA) that are part of the SYSADMIN server role (all Windows Accounts and SQL Server accounts).
• We have set an extremely complex password for the SA account that is hard to guess or remember.
• Perform routine maintenance of SQL Server under a domain user account that has Database Owner (DBO) permissions on the user’s database but does not have SYSADMIN privileges on the system.
• You have not recorded the SA password anywhere so that others do not recognize it. Writing down passwords is not the best approach.

However, since you set such a complex password for SA and used your domain account rather than your SA account in your daily work with SQL Server databases, the unimaginable happened. You have forgotten your SQL Server SA account password.

You are the only person in your group who knew the SA password. Now you can’t remember what it was like and you need to make some server-level confirmations in the configuration windows of your production SQL Server. What do I do now? Here are some options.

• Try to login as SA, specifying all sorts of passwords that will come to your mind.
• Look for the SA password on your computer’s hard drive or in your e-mail (you may have saved it in a file; this is a bad approach, but it can help).
• Try to restore the Master database from the backup. This won’t help in the long run, because if you don’t remember your SA password, you will experience the same problems.
• Re-form the Master database. This will do little to help as you will lose all system and server level settings and configurations, including accounts, permissions, and all server level objects.
• Reinstall SQL Server 2012 and attach all user databases. This may not work because you will experience the same problems as when you reshape the Master database.

Suppose that all attempts to log on to the system under the SA account failed. It’s time to call for backup: the Microsoft Product Support Team. Here’s what its employees have to offer.

SQL Server 2012 has a loophole that allows SYSADMIN access to the production SQL Server. However, you need your Windows account to be a member of the local Windows Server Administrators group that runs the SQL Server 2012 services.

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

Below are the operations you need to perform to take control of SQL Server 2012 as SA.

1. Run the instance of SQL Server 2012 in single-user mode from the command line, and you should enter the command line window as an administrator. You can also run SQL Server 2012 in a minimal configuration and SQL Server will also run in single-user mode.
2. On the command line (Run as Administrator) type: SQLServr.Exe -m (or SQLServr.exe -f) to start the SQL Server 2012 database kernel. Do not close this command line window. It is possible that SQLServr.exe is located in the Binn folder specified in the PATH environment variable. If the Binn SQL Server 2012 folder is not specified in PATH, you can always go to the Binn folder from SQL Server 2012 according to your computer’s directory structure. Normally, the Binn folder is located in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>.
3. Once you start the SQL Server 2012 service in single-user mode or in a minimal configuration, you can open another command line window under the administrator and run the SQLCMD command from there to connect to a SQL Server 2012 instance:

SQLCMD -S <Server name \ Instance name>

For example SQLCMD -S “SALEEMHAKANI”

So, you are logged in to the SQL Server. Note that you are logged in as an administrator in a SQL Server 2012 SALEEMHAKANI instance.

4. After logging into SQL Server 2012 from the command line window using SQLCMD, you can create an account and grant it any server-level permissions. Create a SQL Server 2012 account named “SQL_SALEEM” and then add it to the SA server role.

To create an account from the command line after step 3, please follow the steps below:

1> CREATE LOGIN ‘<Entry_name>’ with PASSWORD='<Password>

2> GO

For instance:

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

2> GO

After creating the “SQL_SALEEM” account, add it to the SYSADMIN server role of the SQL Server 2012 instance. In the same command line window, execute the following operator:

1> SP_ADDSRVROLEMBER ‘<Entry_name>’, ‘SYSADMIN’

2>go.

For instance:

1> SP_ADDSRVROLEMEMBER SQL_SALEEM,’SYSADMIN’

2> GO

The above operator provides SYSADMIN privileges to the “SQL_SALEEM” account.

5. After successful execution of the previous operations, stop the SQL Server services and restart them with the usual parameters. Now you do not need -f or -m parameters.

6. Log in to SQL Server 2012 Management Studio. You can also log in from the command line window by specifying your “SQL_SALEEM” account and the corresponding password. You now have access to your SA instance of SQL Server 2012. You can change the SA password and change the parameters of your production SQL Server.

It is perfectly normal to forget your password from time to time, and this does not mean that even a small inconvenience will occur. The recovery measures I have described will allow you to ensure that your SQL Server is up and running without any downtime or password decryption.