Wednesday, June 17, 2009

Windows Powershell - Changing SQL services password for a number of SQL servers

The below code doesnt have any restrictions of sql services which are in stopped mode. It can change the password for all the sql services that are running and skip the password change for the sql services that are in stopped state.

Here, ComputerListAll.txt contains the list of all the servers that are running sql services. Using the ForEach helps us to change the password simultaneously for all the machines.

$username = "ServiceAccountName"
$password = "ServiceAccountPassword"
$computers = get-content "C:\Powershell\computerlistall.txt"
forEach ($computer in $computers)
{
$sqlservices = Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -computername $computer -Class SqlService `
| Where-Object {$_.StartName -eq $username}
$i = $sqlservices.Count

For ($a = 0 )
{
if ($a -lt $i -and $sqlservices[$a].state -ne 1)

{
$sqlservices[$a].stopservice()
$sqlservices[$a].setserviceaccount($username,$password)
$sqlservices[$a].startservice()
}
else
{
$a++
}

if ($a -eq $i)
{
break
}

if ($sqlservices[$a].State -ne 1)
{
$sqlservices[$a].stopservice()
$sqlservices[$a].setserviceaccount($username,$password)
$sqlservices[$a].startservice()
$a++
}
else
{
$a++
}
if ($a -eq $i)
{
break
}
}
}


Write-Host "SQL passwords changed completely"

No comments:

Post a Comment