How To Change MSSQL ‘sa’ Password On The Windows Command Line

As we know ‘sa‘ login is the default administrative user for MSSQL server. It is therefore very essential to regularly change the password for security reasons. Below is two ways approach in changing the ‘sa’ or other user’s password easily on the command line.

 

SOLUTION 1:

Execute the following commands from the Windows prompt of your server:

a) First list all the MSSQL servers near you with this command.

osql -L

b) Connect to your server found (along with others) on the list using an administrator account.

osql -S servername -E

Note: The servername consist of the ‘HOSTNAME\INSTANCE’ (example ‘ZEUS\HOSTDB’)

whereby ZEUS is the hostname and HOSTDB the instance

c) Enter the following query commands to change the ‘sa’ pasword.

sp_password NULL,’new_password’,’sa’

go

d) Quit and exit the Windows command line.

quit

exit

Try now to connect to the MSSQL server using ‘sa’ with the new password.

 

SOLUTION 2:

I have written a batch script to automate the process.

So, open up the notepad or any other editor your prefere and enter the following:

REM ========================== START ==========================

 

@echo off

SetLocal EnableDelayedExpansion

mode con:cols=78 lines=30

color 3

TITLE MSSQL PASSWORD CHANGER

:User

REM Prompt the user for the user name

cls

echo.

set User=

set /P User=”Enter the user name: ”

if not defined User goto User

:OldPasswd

REM Prompt the user for the old password

cls

echo.

set OldPasswd=

set /P OldPasswd=”Enter the old password: ”

if not defined OldPasswd goto OldPasswd

:NewPasswd

REM Prompt the user for the new password

cls

echo.

set NewPasswd=

set /P NewPasswd=”Enter the new password: ”

if not defined NewPasswd goto NewPasswd

:ChangePasswd

cls

echo.

echo Wait while the new password is been implemented…

ping -n 3 127.0.0.1 >nul

osql -U %User% -P “%OldPasswd%” -Q “sp_password NULL,%NewPasswd%,%User%

echo.

echo The passowrd for %User% has been changed.

echo Press any key to end the program!

pause>nul

echo.

echo bye ^;-^)

ping -n 3 127.0.0.1 >nul

goto :EoF

REM =========================== END =============================

Copy and save this as a batch file with the name “ChgSQLPasswd.bat” or click ChgSQLPasswd.bat to download it


Posted in MSSQL, SQL, Windows and tagged , , , , , , by with comments disabled.