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.

Importing SQL Server 2005 Registered Servers to SQL Server 2008

One thing I have noticed trying to import SQL Server 2005 Registered Servers is, it always fail with errors. The import function is simply isn’t good enough.

To work around this, just do the following:

a) On the SQL Server 2005, shutdown the SQL Server Management Studio (SSMS) and copy out “RegSrvr.xml” from this location

      “%AppData%\Microsoft\Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml”

b) Paste the copied file in the same location where SQL Server 2008 is installed

    Windows 2003:

    “%AppData%\Microsoft\Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml”

    Windows 2008:

    “%UserName%\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml”

    **) You may have to create the folders: 90 \Tools\Shell

c) Open SSMS 2008 and right click “Local Server Groups” in the “Registered Servers” window

d) Choose “Tasks | Previous Registered Servers”

 

**) It is important to point out that the file “RegSrvr.xml” should be edited to clear all password references before copying into SQL Server 2008 installation folder.


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