How to Update Microsoft SQL Account Password

Paopatsin/ August 16, 2020/ Database, MS SQL, Technology

Please read our Disclaimer

Works With
-Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
-Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)

If you are an Database Administrator chances are you’ve came across few request adding new users to the database, aside, the company should have stricter rules implementing security across the organization as such keeping passwords up to date and adherence to security policy is a must. In this post, I will show you several ways on how to change the password for any Microsoft SQL Account.

OPTION 1: Updating via SSMS Context Menu
Steps and Sample Usage:

  1. Open and log into your SQL Management Studio
  2. Connect to your Database
  3. On your Object Explorer, choose your database
  4. Go to Security > Logins
  5. Right-click the SQL Account you wish to change > Choose Properties
  1. On the Login Properties window, update Password and Confirm Password fields
  1. Click OK

PS. Once you’ve accepted, this will take effect immediately thus every script, jobs and running session will have to re-login to continue.

OPTION 2: Updating via querying built-in sp_password
Command:

EXEC sp_password NULL, [New Password], [SQL Account to be Changed] GO

Steps and Sample Usage:

  1. Open and log into your SQL Management Studio
  2. Open a New Query Windows or (CTRL+N)
  3. Copy and Paste the code block to your Query Window

–Update [sa] Password
EXEC sp_password NULL, ‘D1ff1cultP@zzw0rd’, ‘sqlReader’
GO

  1. Execute the query by pressing F5.
    PS. This will take effect immediately new sessions will require the new password.

OPTION 3: Updating via Windows Command Prompt and OSQL
Steps and Sample Usage:

  1. Open your Windows Command Window (Window+R) then type cmd
  2. On the Command Window, type the following command:
    –using -E switch or Trusted Connection(using your Windows Authentication)

osql -S [ServerName\NamedPipes] -E

eg. osql -S (local)\sql2k16 -E

OR
–using SQL Credential

osql -S [ServerName\NamedPipes] -U [SQL Account] -P [SQL Password]

eg. osql -S (local)\sql2k16 -U sqlReader -P D1ff1cultP@zzw0rd

PS. You might encounter some error such as OSQL is not installed or Error Locating Server via OSQL etc.. I have written an article on how to fix the here

  1. On the osql prompt, run the following command:
    EXEC sp_password NULL, ‘D1ff1cultP@zzw0rd’, ‘sqlReader’
    GO
  2. You will see the following result.

Using Trusted Connection

Or using SQL Credentials

PS. This will take effect immediately new sessions will require the new password.

Use Case:

  1. Keeping your SQL password secured and up-to-date.

Caveat:

  1. Changing password requires elevated authorization. You will need Administrative access to be able to change SQL Password.
  2. Changing the password takes effect immediately.
  3. Changing password should be scheduled and communicated to stake-holders otherwise it can break existing SQL session.
  4. Although, the current session can continue, opening a new one will require you to provide the new password.

PS. My favorite is Option #3, as a Database Administrator, its faster and sleek.. I was born in the pre-historic times wherein computing displays was just a combination of black and white colors and memorizing commands is way to go 🙂

Share this Post