PowerShell :: SQL SELECT returns only first row

Question

I have a strange feeling I'm doing something wrong here.

This is my PowerShell script, of course stolen and adapted from here:

param(
    $instance = "localhost"
)

if (!(Get-Module -ListAvailable -Name "SQLPS")) {
    Write-Host -BackgroundColor Red -ForegroundColor White "Module Invoke-Sqlcmd is not loaded"
    exit
}

#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted,[string]$username,[string]$password){
    if($trusted -eq 1){
        try{ 
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop -ConnectionTimeout 5 -QueryTimeout 0      
        }
        catch{
            Write-Host -BackgroundColor Red -ForegroundColor White $_
            exit
        }
    }
    else{
        try{
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop -ConnectionTimeout 5 -QueryTimeout 0
        }
         catch{
            Write-Host -BackgroundColor Red -ForegroundColor White $_
            exit
        }
    }
}

function List-of-Databases([string]$instance,[int]$trusted,[string]$login,[string]$password){
    $ctpQuery = "
    SELECT name 
    FROM master.dbo.sysdatabases
    "

    return $(Execute-Query $ctpQuery "master" $instance $trusted $login $password)[0]
}

function Check-SysAdminLogins([string]$instance,[int]$trusted,[string]$login,[string]$password){
    $sysadminLoginsQuery = "
    SELECT   name
    FROM     master.sys.server_principals 
    WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
    ORDER BY name
    "
    return $(Execute-Query $sysadminLoginsQuery "master" $instance $trusted $login $password)
}


$loginChoices = [System.Management.Automation.Host.ChoiceDescription[]] @("&Trusted", "&Windows Login", "&SQL Login")
$loginChoice = $host.UI.PromptForChoice('', 'Choose login type for instance', $loginChoices, 0)
switch($loginChoice)
{
    1 { 
        $login          = Read-Host -Prompt "Enter Windows Login"
        $securePassword = Read-Host -Prompt "Enter Password" -AsSecureString
        $password       = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
      }
    2 { 
        $login          = Read-Host -Prompt "Enter SQL Login"
        $securePassword = Read-Host -Prompt "Enter Password" -AsSecureString
        $password       = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
      }
}

#Attempt to connect to the SQL Server instance using the information provided by the user
try{
    switch($loginChoice){
        0       {$buildNumber = Execute-Query "SELECT 1" "master" $instance 1 "" ""}
        default {$buildNumber = Execute-Query "SELECT 1" "master" $instance 0 $login $password}   
    }     
}
catch{
    Write-Host -BackgroundColor Red -ForegroundColor White $_
    exit
}

#If the connection succeeds, then proceed with the verification of best practices
Write-Host ""
Write-Host "------------ ------------ ------------ ------------" 
Write-Host "************ ************ ************ ************" 
Write-Host "------------ ----         ----         ------------" 
Write-Host "    ****     ************ ************     ****    " 
Write-Host "    ----     ------------ ------------     ----    " 
Write-Host "    ****     ****                *****     ****    " 
Write-Host "    ----     ------------ ------------     ----    " 
Write-Host "    ****     ************ ************     ****    " 
Write-Host ""
#List of Databases
Write-Host "#####################"
Write-Host "# List of Databases #"
Write-Host "#####################"           
switch($loginChoice){
    0       {$lod = List-of-Databases $instance 1 "" ""}
    default {$lod = List-of-Databases $instance 0 $login $password}   
} 
foreach($database in $lod)
{
    Write-Host $database.name | Format-Table
}
Write-Host ""

#Logins with sysadmin privilege
Write-Host "##################################"
Write-Host "# Logins with sysadmin privilege #"
Write-Host "##################################"
switch($loginChoice){
    0       {$sysadminLogins = Check-SysadminLogins $instance 1 "" ""}
    default {$sysadminLogins = Check-SysadminLogins $instance 0 $login $password}   
} 
foreach($sysadminLogin in $sysadminLogins)
{
    Write-Host $sysadminLogin.name
}

Write-Host ""

If you run it the scripts gets executed successfully :

The problem is that the second query:

SELECT   name
FROM     master.sys.server_principals 
WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name

correctly returns all rows:

##################################
# Logins with sysadmin privilege #
##################################
NT SERVICE\SQLSERVERAGENT
NT SERVICE\SQLWriter
NT SERVICE\Winmgmt
sa

while the first query:

SELECT name 
FROM master.dbo.sysdatabases

only returns the first row of the SELECT statement, of course the master database:

#####################
# List of Databases #
#####################
master

But as you can see I have plenty of databases in there.

Where am I wrong?

Answer 1

Note the array index at the end of the return statement:

return $(Execute-Query $ctpQuery "master" $instance $trusted $login $password)[0]

That will return only the first row. Try:

return $(Execute-Query $ctpQuery "master" $instance $trusted $login $password)