Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

Granting the CONNECT permission to the HADR endpoint


In this recipe, we will grant the CONNECT permission to an existing SQL Server principle.

Getting ready

Identify the SQL Server principle that you will use for AlwaysOn that will need the CONNECT permission.

How to do it...

Perform the following steps to grant the CONNECT permission to an endpoint:

  1. Open PowerShell ISE as an administrator.

  2. Add the following script and run it:

    Import-Module SQLPS -DisableNameChecking
    
    $instanceName = "SQL01"
    $endpointName = "AlwaysOnEndpoint"
    $endpointAccount = "QUERYWORKS\sqlservice"
    
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $instanceName
    $endpoint = $server.Endpoints[$endpointName]
    
    #identify the Connect permission object
    $permissionSet = New-Object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet([Microsoft.SqlServer.Management.Smo.ObjectPermission]::Connect)
    
    #grant permission
    $endpoint.Grant($permissionSet,$endpointAccount)

How it works...

To assign permissions to an endpoint using...