Posts Tagged ‘reset sa password in sql server 2012’

2 Options to Reset Forgotten SQL Server 2012 Password

June 10th, 2015 by Admin

Forgot the SA password for your SQL Server 2012 instance? If you have tried to access the database too many times with wrong passwords, the SA account may be locked out. In this tutorial we’ll show you two options to take control of your SQL Server 2012 by either adding a sysadmin account or resetting the SA password.

Option 1: Reset SQL Server 2012 Password in Single-user Mode

  1. Launch the SQL Server Configuration Manager utility. Look for your SQL Server 2012 instance and stop the service.

    stop-sql-server

  2. Open a Command Prompt with admin privileges, run the following command to start SQL Server 2012 in single-user mode:
    net start MSSQL$SQLEXPRESS /m"SQLCMD"

    Be sure to change MSSQL$SQLEXPRESS to the service name of your SQL Server instance. Each instance has its own SQL Server service. The service name could be found within the Windows Services panel (services.msc).

    sql-server-service

  3. Use the sqlcmd command to connect to your SQL Server 2012 database locally. If your SQL Server is installed on a remote computer, make sure you substitute the . (dot) with the host name of that server.
    sqlcmd -E -S .
  4. Type the following SQL statement to create a new account and granting it any server-level permission.
    CREATE LOGIN tom WITH PASSWORD = 'P@ssw0rd'
    GO
    ALTER SERVER ROLE sysadmin ADD MEMBER tom
    GO

    add-sql-user

  5. Type exit to quit sqlcmd. Restart your SQL Server 2012 service to get out of the single-user mode. You can then connect to the database using the new login you just created.

Option 2: Offline Reset SQL Server 2012 Password

SQL Server creates the user’s password hash, which is stored in the master database (sysxlogins). With SQL Server Password Changer you can unlock / reset SQL Server 2012 passwords for the SA and other user accounts. Here’s how:

  1. First of all, you need to stop your SQL Server 2012 instance from within the SQL Server Configuration Manager utility.
  2. Download and install the SQL Server Password Changer program.
  3. Start the SQL Server Password Changer program. Click on Open File button to browse for the master.mdf file. Usually the master.mdf file is located at C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA.

    open-master-mdf

  4. Once you have clicked on the Open button, the program will display a list of user accounts for your SQL Server instance. Select the SA account and click on Change Password button.

    sql-server-2012-password

  5. In the pop-up window, type a new password and click OK. The new password can be as short or simple as you want, no need to meet the password complexity requirements.
  6. After resetting the SA password, start your SQL Server instance and you can then login to the SA account successfully.