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 change, change sa password, mssql, password, sa, sql, windows command line by Stan with comments disabled.