Featured image of post Viewing SQL Endpoint Permissions with PowerShell

Viewing SQL Endpoint Permissions with PowerShell

A quick and simple post today as I have been very busy. I needed to list the users with permissions on mirroring endpoints today so I wrote this script and figured it was worth sharing.

It’s a simple script which takes a server name from a Read-Host prompt. Displays the available endpoints and asks which one you want and shows you the permissions

$Server = Read-Host "Please Enter the Server"
$Endpoints = $srv.Endpoints |select Name -ExpandProperty Name
$EndpointName = Read-Host "Please Enter the Endpoint Name `n Available Names are `n $Endpoints"
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
$Endpoint = $srv.Endpoints[$EndpointName]

and heres a screenshot of the results


If you want to do it with T-SQL

select s.name as grantee,
e.name as endpoint,
p.permission_name as permission,
p.state_desc as state_desc
from sys.server_permissions p
join sys.server_principals s on s.principal_id = p.grantee_principal_id
join sys.endpoints e on p.major_id = e.endpoint_id
where p.type='CO'


Built with Hugo
Theme Stack designed by Jimmy