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]
$Endpoint.enumObjectPermissions()

and heres a screenshot of the results

Ps`1

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'

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s