Using PowerShell to get Azure Endpoint Ports

A quick blog today. I was reading this blog post about How to read the SQL Error Log and I thought I would try some of the examples. I started my Azure VM using the steps in my previous post

I ran

Get-AzureVM -ServiceName TheBestBeard -Name Fade2black

and saw my box had started


and then

Get-AzureVM -ServiceName TheBestBeard -Name Fade2black|Get-AzureEndpoint |Format-Table -AutoSize


and bingo I had my SQL Port to put in SSMS and can go and play some more with SQL

Alter SQL Mirroring Endpoint Owner with Powershell

Whilst using my Drop-SQLLogins function, which is one of my PowerShell Box Of Tricks series, it failed to delete logins on some servers with the error

Login domain\user’ has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)

I used the Show-SQLPermissions function and added the .grantor property to try and locate the permission the account had granted but it came back blank. A bit of googling and a AHA moment and I remembered mirroring

I checked the mirroring endpoints

mirroring endpoitn check

and found the endpoints with the user as the owner so I needed to change them

This can be done in T-SQL as follows

alter endpoint

but to do it on many endpoints it is easier to do it with Powershell


I could then drop the user successfully

$svrs = ## list of servers Get-Content from text fiel etc

foreach ($svr in $svrs) {
    $server = New-Object Microsoft.SQLServer.Management.Smo.Server $svrs
    foreach ($endpoint in $server.Endpoints['Mirroring']) {
        if ($endpoint.Owner = 'Domain\User') {
            $endpoint.Owner = 'Domain\NEWUser'