Use PowerShell to Grant SysAdmin Role to Certain Users

$principle=$env:USERDOMAIN+'\Domain Admins'
$sqlServer=$env:computername

function includeSqlTools{
  $ErrorActionPreference='stop'
  try{
    $trustedPsgallery=(Get-PSRepository PSGallery).InstallationPolicy -eq 'Trusted'
    if(!$trustedPsgallery){
        Set-PSRepository -Name PSGallery -InstallationPolicy Trusted 
    }
    if(!(Get-Module sqlserver)){
        Install-Module sqlserver -Confirm:$False
    }
    if(!(Get-Module dbatools)){
        Install-Module dbatools -Confirm:$False
    }
    Import-Module sqlserver  
    Import-Module dbatools
    return $true
  }catch{
    write-warning $_
    return $false
  }
}

function grantSysadmin($principle,$sqlServer=$env:computername){
    if(!(includeSqlTools)){
        write-warning "Unable to proceed without SQL Tools"
        return $false
    }
    $sqlPortOpen=test-netconnection $sqlServer -port 1433 -informationlevel quiet
    $result=.{if($sqlPortOpen){
      try{
        $server=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
        $sysadmins=$server.Roles|?{$_.Name -eq 'sysadmin'}
        $sysadmins.AddMember($principle)
        write-host "Current sysadmin principles:`r`n$(($sysadmins.EnumMemberNames()|out-string).trim())"
        return $true
      }catch{
        Write-Warning
        return $false
      }
    }else{
      # Workaround for firewall issues blocking SQL port between jump host and SQL Server
      invoke-command -computername $sqlServer {
        param($principle,$includeSqlTools)
        $sqlTools=[scriptblock]::create($sqlTools).invoke()
        if(!$sqlTools){
          write-warning "Unable to proceed without SQL Tools"
          return $false
        }
        try{
          $server=New-Object Microsoft.SqlServer.Management.Smo.Server("(localhost)")
          $sysadmins=$server.Roles|?{$_.Name -eq 'sysadmin'}
          $sysadmins.AddMember($principle)
          write-host "Current sysadmin principles:`r`n$(($sysadmins.EnumMemberNames()|out-string).trim())"
          return $true
        }catch{
          write-warning $_
          return $false
        }
      } -Args $principle,${function:includeSqlTools}
    }
  }
  return $result   
}

grantSysadmin $principle $sqlServer

Leave a Reply

Your email address will not be published. Required fields are marked *