?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
$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
Categories: