How to reset the SA Password in SQL Management Studio

Version 7

    Purpose

     

    The purpose of this article is to show how to reset the SA password for a SQL Express database in SQL Management Studio. This should only be used when this is the only option left to get the DBO to give a user rights to the Protect database.

     

    Description

     

    NOTE: It is highly recommended to perform a backup of the registry before performing any modifications: How To Back-up The Registry

     

    1. Open the registry editor by going to Run > regedit.exe.

     

    2. Navigate to:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer
    

    NOTE: Your branches under Microsoft SQL Server may be different so you may need to verify which to choose before editing.

     

    3. Change the value of LoginMode from 1 to 2.

     

    4. Close the registry editor.

     

    5. Open Services either through Control Panel or Run > Services.msc

     

    6. Find the “SQL Server (SQLEXPRESS)” entry and open its properties

    • Stop the service
    • Enter “-m” into the “Start parameters” field
    • Start the service

     

    7. Open a Command Prompt and enter the command:

    osql -S PC_NAME\SQLEXPRESS -E
    

    NOTE: Be sure to change PC_NAME to whatever your PC name is or you will get an error.

     

    8. At the next prompts enter the following commands:

    1> alter login sa enable
    2> go
    3> sp_password NULL,'new_password','sa'
    4> go
    5> quit
    

    NOTE: Be sure to input a password for 'new_password' with the punctuation and be sure to make note of what this new password is.

     

    9. Open Services either through Control Panel or Run > Services.msc

    • Stop the “SQL Server (SQLEXPRESS)” service.
    • Remove the “-m” from the Start parameters field.
    • Start the service.

     

    10. Log into SQL Management Studio with the sa username and the new password that you input from Step 8.

     

    11. Give the necessary permissions to the users using the following article: SQL Account Configuration - Privilege or Role Requirements for Protect Database

     

    Additional Information

     

    NOTE: A user who has insufficient rights to the database may be able to log in to SQL Management Studio, but they will be unable to assign privileges or roles to themselves until a database admin with sufficient privileges does so. This article is for those who only have the SA user as the last database admin to give privileges. If you have a database admin, you should consult them first before doing the procedure above. This is run at the user's own risk.

     

    Affected Product(s)

     

    Shavlik Protect 9.x

    SQL Server Management Studio 20xx