How to change the SA login password using powershell.

Question

Post reply

How to change the SA login password using powershell.

Eswin

SSCrazy Eights

Points: 8320
More actions
July 17, 2012 at 4:23 am

#277871

Hi Experts,
I’m looking To Alter the SA logins in more than 50 MSSQL servers. Each server has different SA logins.
Is this possible using powershell. Or is there any other method?
/Eswin
Tanx 😀

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50118 More actions · Answer 1

I found a couple suggestions on this, with a quick Google, including one from this site:

SQL Server Central Topic

MSDN Forums with using a batch file or Powershell

And an "Ask SQL Server Central" Topic

It seems one of the most common points though, is don't set all your SA the same, as it's a rather large security risk...

I'd stop and consider, do you *REALLY* need for all the SA to be the same, other than to make it easier to sign into the various servers?

Jason

bruce 1565 SSCrazy Points: 2574 More actions · Answer 2

While I agree with Jason here's a way to change the sa passwords and still have different passwords. The Powershell script reads the server name and password from a text file with one server and password per line. The server and passwords are separated with a colon, like this.

server0:password07

server1:password08

server2:password09

server3:password10

Makes it easy to have different sa passwords and still change multiple SQL Servers at once.

In this example the text file is named serverList.txt, on line 2. Just change it to whatever you name your file. And don't forget to delete that file once you're finished since it has your new sa passwords in clear text.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop

Foreach ($list In $(Get-Content -Path serverList.txt)) {

$server = $list.split(":")

$srvObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server[0]

$srvObject.Logins.Item('sa').ChangePassword($server[1])

$srvObject.Logins.Item('sa').Alter()

}

raju400 SSC Enthusiast Points: 157 More actions · Answer 3

hi bruce 1565

need your help regarding changing sa passwords nearly 450 sql machines....with same or diff passwords....

is tat enough jus changing text file name in the below script or i hv to change any server or instance name ??? pls giv me in detail as im new to this

many thanks.

raj