All Webs and Site Templates in use within a Site Collection PowerShell Script

$site = Get-SPSite “http://yoursite”

foreach ($web in $site.AllWebs) {

$web | Select-Object -Property Title,Url,WebTemplate

}

$site.Dispose()

Advertisements

Get list of all users present in a site under different SharePoint Groups

Background:

I wanted to see all users who have access to the SharePoint site and belongs to which SharePoint group.

Solution:

Below SharePoint powershell script helps me to get list of users group wise-

$site = Get-SPSite <Provide Site Collection URL here>
$web = $site.OpenWeb()
$groups = $web.sitegroups
 
foreach ($grp in $groups) {
    "Group: " + $grp.name;
    $groupName = $grp.name
    write-host "Group: " $groupName   -foregroundcolor green
    foreach ($user in $grp.users) {
            "User: " + $user.name
            write-host "User " $user.UserLogin   -foregroundcolor red
    }
}

Usage:

  • Navigate to Script location and type below command-
GetUsers.ps1

This will show output on screen.

  • Navigate to Script location and type below command-
GetUsers.ps1 | Out-File C:Output.csv

This will generate output as csv file. “C:Output.csv” is the file name and location where generated csv file is saved. Please change as per your requirement.

Access Requests Explained for SharePoint 2013 – With a Script to Assign Default Groups

One of the features of SharePoint that has been around is the ability for users that need access to a site, and are denied access, through the “Request Access” process.

To enable or review these settings,

  • go to “Settings” > “Site Settings” > “User and Permissions” and click “Access Request Settings”. In the “Access Request Settings” dialog box, select the check box next to “Allow access requests” then provide an email address of the individual you’d like to manage this feature.
  • If a site has multiple groups with the same permission levels (Owners, Members and Viewers) but there is not an assigned default group, then you will see the problem where access requests will either not display for the impacted user or an owner will not be able to approve requests.

Here’s a Windows PowerShell script to change each of the groups for a site so that each is identified as the default group for Members, Owners and Visitors

You’ll need to a the “Microsoft.SharePoint.PowerShell” add-in at the top of the script to get the SharePoint references.

#Members Group
$web = Get-SPWeb “
https://sharepoint.contoso.com
$groupToMakeDefaultMembersGroup = $web.Groups | ? { $_.Name -eq “Team Site Members” }
$web.AssociatedMemberGroup = $groupToMakeDefaultMembersGroup
$web.Update()

#Owners Group
$web = Get-SPWeb “
https://sharepoint.contoso.com
$groupToMakeDefaultOwnersGroup = $web.Groups | ? { $_.Name -eq “Team Site Owners” }
$web.AssociatedOwnerGroup = $groupToMakeDefaultOwnersGroup
$web.Update()

#Visitors Group
$web = Get-SPWeb “
https://sharepoint.contoso.com
$groupToMakeDefaultVisitorsGroup = $web.Groups | ? { $_.Name -eq “Team Site Visitors” }
$web.AssociatedVisitorGroup = $groupToMakeDefaultMembersGroup
$web.Update()

#Enable Access Requests after it was disabled
$web.RequestAccessEmail = “user@mydomain.com”
$web.Update()

If you turn off the feature, you will can re-enable the feature by adding an email address to the “RequestAccessEmail” property.

Hope this helps solving the problem around assigning default groups and enabling the Request Access feature in SharePoint 2013.

Configuring SQL Server 2014 AlwaysOn Availability Group using PowerShell

Introduction

Earlier on I was trying to search for some scenarios to implement AlwaysOn Availability Groups using PowerShell, although I was able to find some interesting post however non of them represented a complete scenario starting from a fresh windows server installation (using PowerShell), so I decided to write this blog as quick walkthrough thePowerShell scripts needed.

Sample Scenario

In this scenario I will be using a 2 Nodes setup with the following software already installed:

  • Windows Server 2012 R2
  • Both Servers are joined to the domain

Configuration Steps:

To make the scenario simple I be using a Domain Account that has a Local Administrator Permission on both nodes. In addition, all scripts below will be running using PowerShell (Run as Admin) so Lets get started:

1- Install SQL Server

We need to install a standalone setup on each node. I will do that using the below sample unattended SQL Setup Script:

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainNameUserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainNameUserName>" /AGTSVCACCOUNT="<DomainNameUserName>" /AGTSVCPASSWORD="<StrongPassword>"   /IACCEPTSQLSERVERLICENSETERMS

2- Add Windows Failover Cluster

We need to install it on each node. I will do that using the below script:

Import-Module ServerManager

Add-WindowsFeature Failover-Clustering –IncludeAllSubFeature

Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

3- Configure Windows Failover Cluster

Run the below script on the 1st Node and make sure to update the configuration parameters with your values:

#—————————————————-

Configuration Parameters

#—————————————————-
$Server1 = “Server1”
$Server2 = “Server2”
$IPAddress = “X.X.X.X”
$AlwaysOnClusterName = “ClusetrName”
$QuorumFileSharePath = “\FileSharePath”

#—————————————————-

Create Cluster Service

Create a new cluster ‘AlwaysOnCluster’ with nodes.

#—————————————————-
Import-Module FailoverClusters
New-Cluster –Name $AlwaysOnClusterName –Node $Server1,$Server2 -StaticAddress $IPAddress -NoStorage

Add Quorum

Set-ClusterQuorum -NodeAndFileShareMajority $QuorumFileSharePath

4- Configure AlwaysOn Failover Cluster

Now for the fun part, I have configured the whole AlwaysOn Configuration in one script that needed to be ran on the 1st Node. In addition, I have added the proper comments so take your time to review it  and make sure to update the configuration parameters with your values:

#Set execution policy
Set-ExecutionPolicy unrestricted

Import SQLPS Module

Import-Module “sqlps” -DisableNameChecking

#—————————————————-

Configuration Parameters

#—————————————————-
#Server Names
$Server1 = “Server1”
$Server2 = “Server2”
$ServerInstance1 = ‘$Server1Instance’
$ServerInstance2 = ‘$Server2Instance’

#AlwaysOn TempDB Names
$tmpDB1 = “AG1-TempDB”

#Availability Group Names
$AG1Name=”AG-1″

#Availability Group Listener
$AGListner1Name = “Listener1”
$Listner1IP_Mask = “X.X.X.X/X.X.X.X”
$ListnerPort= “1433”
$FileSharePath = “\FileSharePath”

#Service Accounts
$SQLSVCAccount = “DomainSVCAccount”
$AGDomain = “.contoso.com” #Keep the ‘.’ before domain name

#AlwaysOn EndPoints
$AOEndpointName = “AlwaysOnEndpoint”
$AOPort = “5022”
$AOEncryptionAlgorithm = “AES”

#—————————————————-

Enable AlwaysOn on Servers

#—————————————————-
Enable-SqlAlwaysOn –ServerInstance $Server1
Enable-SqlAlwaysOn –ServerInstance $Server2

#—————————————————-

Create Endpoints

#—————————————————-
#####1st Server
$AOtmpPath = “SQLSERVER:SQL$Server1default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

start the endpoint

$AOtmpPath = “SQLSERVER:SQL$Server1defaultendpointsAlwaysOnEndpoint”
Set-SqlHadrEndpoint –Path $AOtmpPath –State “Started”;

####2nd Server
$AOtmpPath = “SQLSERVER:SQL$Server2default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

start the endpoint

$AOtmpPath = “SQLSERVER:SQL$Server2defaultendpointsAlwaysOnEndpoint”
Set-SqlHadrEndpoint –Path $AOtmpPath –State “Started”;

#—————————————————-

Grant Permissions for EndPoints

#—————————————————-
$SQLPermissionQry = ”
USE [master]
GO
CREATE LOGIN $SQLSVCAccount FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT ALTER ANY AVAILABILITY GROUP TO $SQLSVCAccount
GO
GRANT CONNECT SQL TO $SQLSVCAccount
GO
GRANT VIEW SERVER STATE TO $SQLSVCAccount
GO

Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance1
Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance2

#—————————————————-
#Create Temp DB for AG
#—————————————————-
$AOtmpPath = “SQLSERVER:SQL$Server1default”
$svr = Get-Item $AOtmpPath
$db1 = New-Object Microsoft.SqlServer.Management.Smo.Database($svr, $tmpDB1);
$db1.Create();

#—————————————————-
#Initial Backup for the DB
#—————————————————-
cd “SQLSERVER:SQL$Server1defaultdatabases”
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1;

#————————————————

Backup & Restore TempDBs to prepare for AlwaysOn

#————————————————
#Backup
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupFile “$FileSharePath$tmpDB1.bak”;
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupAction Log –BackupFile “$FileSharePath$tmpDB1.trn”;

Restore

cd “SQLSERVER:SQL$Server1default”
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –BackupFile “$FileSharePath$tmpDB1.bak” –NoRecovery;
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –RestoreAction Log –BackupFile “$FileSharePath$tmpDB1.trn” –NoRecovery;

#———————————————
#Create AG Replica
#It assumes SynchronousCommit + Automatic Failover
#———————————————
$PrimaryRepTCP = “TCP://$Server1$AGDomain” + ‘:’ + “$AOPort”
$SecondaryRepTCP = “TCP://$Server2$AGDomain” + ‘:’ + “$AOPort”

$Primary = new-sqlavailabilityreplica -Name $Server1 -EndpointUrl $PrimaryRepTCP -ConnectionModeInPrimaryRole “AllowAllConnections” -ConnectionModeInSecondaryRole “AllowAllConnections” –AvailabilityMode “SynchronousCommit” –FailoverMode “Automatic” -AsTemplate -Version 11;
$Secondary = new-sqlavailabilityreplica -Name $Server2 -EndpointUrl $SecondaryRepTCP -ConnectionModeInSecondaryRole “AllowAllConnections” –AvailabilityMode “SynchronousCommit” –FailoverMode “Automatic” -AsTemplate -Version 11;

#———————————————
#Create a new AG
#———————————————
$ag = New-SqlAvailabilityGroup -Name $AG1Name -AvailabilityReplica ($Primary, $Secondary) -Database $tmpDB1
#———————————————
#Join Availability Replica
#———————————————

$AOtmpPath = “SQLSERVER:SQL$Server2default”
Join-SqlAvailabilityGroup –Path $AOtmpPath –Name $AG1Name;

#———————————————
#Join Replica Database on a Secondary replica
#———————————————
$agpath1 = “SQLSERVER:SQL$Server2defaultAvailabilityGroups$AG1Name”
Add-SqlAvailabilityDatabase –Path $agpath1 –Database $tmpDB1

#———————————————
#Create a Listener using Static IPs
#———————————————
$agpath1 = “SQLSERVER:SQL$Server1defaultAvailabilityGroups$AG1Name”
$ag = Get-Item $agpath1; #Validate AG Path
New-SqlAvailabilityGroupListener -Name $AGListner1Name –Path $agpath1 –StaticIp $Listner1IP_Mask –Port $ListnerPort;