Summary

This article provides a summary on how to reset sa password if system administrator losses the password or access to SQL.

Applies To

This article applies to MachPanel all versions.

Symptoms

A system administrator can lose access to an instance of SQL Server because of one of the following reasons:

1. All logins that are members of the sysadmin fixed server role have been removed by mistake.
2. All Windows Groups that are members of the sysadmin fixed server role have been removed by mistake.
3. The logins that are members of the sysadmin fixed server role are for individuals who have left the company or who are not available.
4. The sa account is disabled or no one knows the password

Resolution

Start the instance of SQL Server in single-user mode by using the -m option. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

Steps:

• Start SQL Server Management Studio. On the View menu, select Registered Servers. (If your server is not already registered, right-click Local Server Groups, point to Tasks, and then click Register Local Servers.)
• In the Registered Servers area, right-click your server, and then click SQL Server Configuration Manager.
• Close Management Studio.
• In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, find your instance of SQL Server. (The default instance of SQL Server includes (MSSQLSERVER) after the computer name. Named instances appear in upper case with the same name that they have in Registered Servers.) Right-click the instance of SQL Server, and then click Properties.
• On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)

Note: For earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Be careful not to change any of the existing parameters. At the very end, add a new parameter ;-m as shown in snapshot below and then click Apply.

• Click OK, and after the message to restart, right-click your SQL server name, and then click Restart as shown in snapshot below:

• After SQL Server has restarted your server will be in single-user mode. Make sure that that SQL Server Agent is not running. If started, it will take your only connection.
• Now Run SQL Server Management Studio as Administrator. Multiple connections will fail because SQL Server is in single-user mode. You can select one of the following actions to reset sa password. Do one of the following.
  • To reset the password of the sa account, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). Change the password of the sa account with the following syntax.
    
          ALTER LOGIN sa WITH PASSWORD = 'Type Your Password Here';
  • Connect with Object Explorer using Windows Authentication (which includes your Administrator credentials). Expand Security, expand Logins, and double-click on SA account and type the new password and click on save button as shown in snapshot below:

 

• Now change the SQL Server back to multi-user mode. The following steps now change SQL Server back to multi-user mode.

1. Close SQL Server Management Studio.
2. In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, right-click the instance of SQL Server, and then click Properties.
3. On the Startup Parameters tab, in the Existing parameters box, select -m and then click Remove.

Note: For earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Remove the ;-m which you added earlier, and then click Apply.

            4. Right-click your server name, and then click Restart.