Functions/PublishProxy.ps1
|
Function Publish-Proxy { param( $SqlConnectionString, $RunAs ) $sql_connection = new-object System.Data.SqlClient.SqlConnection ($SqlConnectionString) Write-Verbose "Checking if proxy $RunAs exists. If not will create..." -Verbose $check = $null try { $sql_connection.Open() $sql_connection.ChangeDatabase("msdb") $sqlExecute = " IF NOT EXISTS (select * from dbo.sysproxies where name = @0) SELECT 'NOTEXISTS' " $sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlExecute, $sql_connection) $sqlCommand.Parameters.AddWithValue("@0", $RunAs) | Out-Null $Check = $sqlCommand.ExecuteScalar() if ($check -eq "NOTEXISTS") { Write-Warning "Creating Proxy $RunAs on instance." $sqlExecute = " EXEC msdb.dbo.sp_add_proxy @proxy_name=@0,@credential_name=@0, @enabled=1 " $sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlExecute, $sql_connection) $sqlCommand.Parameters.AddWithValue("@0", $RunAs) | Out-Null $sqlCommand.ExecuteNonQuery() | Out-Null Write-Verbose "Proxy for $RunAs created!" -Verbose } } catch { Write-Error $_.Exception Throw } if ($check -eq "NOTEXISTS") { try { $sqlExecute = " EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=@0, @subsystem_id=11 " $sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlExecute, $sql_connection) $sqlCommand.Parameters.AddWithValue("@0", $RunAsAccount) | Out-Null $sqlCommand.ExecuteNonQuery() | Out-Null Write-Verbose "permissions for $RunAsAccount updated!" -Verbose } catch { Write-Error $_.Exception Throw } } if ($null -eq $check) { Write-Verbose "Login already created." -Verbose } $sql_connection.Dispose(); } |