PowerShell script to add domain user to multiple servers with results (success / failure)

Question

I am a PowerShell newbie and I am having trouble with a script. Basically, I have created a script that will add a sysadmin to multiple SQL servers listed in a text file. The text file is just one column of server names. The script seems to work fine.

I would like to have a report stating if it failed on any of the servers in the list. This could be csv or text. I just don't want to assume the script ran fine on 100+ servers to only find out later that there were issues with the account or server.

This the script I am using:

Import-Module -Name SqlPs
$servers = Get-Content -Path C:\Temp\servers.txt
$servers |
   foreach{ `
      Invoke-Sqlcmd -ServerInstance $_ `
            -Database 'Master'  `
            -Query "EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\USERNAME', @rolename = N'sysadmin';" `
            -QueryTimeout 10 `
   }

Any assistance would be greatly appreciated.

Answer 1

I would suggest wrapping your command in a try/catch block to aggregate the failures:

Import-Module -Name SqlPs

$errors = [System.Collections.Generic.List[string]]@()

$params = @{
    Database     = 'Master'
    Query        = "EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\USERNAME', @rolename = N'sysadmin';"
    QueryTimeout = 10
    ErrorAction  = 'Stop'
}
foreach ($server in Get-Content -Path C:\Temp\servers.txt) {
    try {
        Invoke-Sqlcmd -ServerInstance $server @params
    } catch {
        $errors.Add("$server failed: $_")
    }
}

$errors > errors.log

The most important thing here is ErrorAction:Stop which will turn your command into a terminating error to be caught by the try/catch block.