Sunday, February 21, 2016

Create Site Collection in SharePoint using PowerShell

We have to use New-SPSite cmdlet to create a site collection in SharePoint. It takes URL and Owner parameters as mandatory with several other parameters. Here is an example:

PowerShell to create new Site Collection in SharePoint 2016:
To create a site collection using PowerShell in SharePoint 2013, use this script.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Variables
$SiteCollURL = "http://intranet.crescent.com/sites/Sales"
$SiteName = "Sales Team Site"
$SiteOwner = "Crescent\Salaudeen"
$SiteTemplate = "STS#0"  #Team Site Template

#Create new Site Collection
New-SPSite -URL $SiteCollURL -OwnerAlias $SiteOwner -Template $SiteTemplate -Name $SiteName


Create blank site collection in SharePoint 2013 using PowerShell 
Use the  template as "STS#1" to create a blank site collection in SharePoint. We can get all available site templates using cmdlet: Get-SPWebTemplates.

SharePoint 2013: PowerShell to create site collection in specific content database
Use -ContentDatabase parameter in New-SPSite to create your new site collection in an existing content database. E.g.
New-SPSite -Name "Sales" -ContentDatabase SP2013_Sales_Content -url http://Intranet.crescent.com/sites/Sales/ -OwnerAlias "Crescent\SPAdmin" -Template "STS#1"

PowerShell to create site collection without template:
In case, you don't provide template value, still the site collection will be created, but when the site is accessed for the first time,  Users will get a prompt to choose the template for the site collection.
Create Site Collection in SharePoint using PowerShell



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, February 20, 2016

SharePoint Online: Delete Unique Permissions for All Items in a List using PowerShell

Requirement: Reset all customized permissions in all documents of a SharePoint online document library.

By default, Permissions are inherited from their parent in all levels of SharePoint. E.g. Sites inherits permissions from its parent site collection (or parent site), lists and libraries inherits permissions from the site, items in the list inherits permissions from the list. So, If you make any change in permissions at the parent level, any child underneath, automatically inherits the permission changes you made in the parent, unless the child is using its own unique permissions.

At times, you may have to setup unique permissions at granular level and of course, you may have to reset the broken permissions.

How to Delete Unique Permissions in SharePoint?
To reset custom permissions on SharePoint list items or documents,
  • Navigate to the SharePoint library where your documents are stored
  • Select the document >> Click on "Shared With" under Manage group in the ribbon 
  • Click on Advanced >> and click on "Delete Unique Permissions" button from the ribbon. Confirm the prompt once!
delete unique permissions in sharepoint online using powershell

Alright, now the permissions are set to Inherited from the parent library of the document. 
But wait, Picking each and every individual document and repeating these steps to remove unique permissions is tedious, wouldn't you agree? So, I wrote this PowerShell script to Reset Broken Inheritance on all items in a SharePoint List.

PowerShell Script to Delete Unique Permissions for All List Items in SharePoint Online:
#Load SharePoint Online Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/Sales/"
$ListName= "Documents"
$UserName= "Salaudeen@crescent.com"
$Password ="Password goes here"
 
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
 
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials
  
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
 
#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$Context.Load($ListItems)
$Context.ExecuteQuery()

Write-host "Total Items Found:"$ListItems.Count
#Iterate through each list item
 $ListItems |  foreach {
    #Delete Unique Permission
    $_.ResetRoleInheritance()
 }
$Context.ExecuteQuery()

Write-host "Broken Permissions are Deleted on All Items!" -ForegroundColor Green
Last but not least: You need SharePoint online Client SDK installed on your client machine to use this code: https://www.microsoft.com/en-us/download/details.aspx?id=42038

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, February 16, 2016

PowerShell-CAML SPQuery to Filter List Items based of Person or Group Field Value

Requirement:
In a project tracking SharePoint list, want to quickly get the list the projects where a particular user is listed under Team members - person or group (People picker) field.

Solution: Use SPQuery with PowerShell as in the below example.

PowerShell to Filter List Items based of Person or Group Field:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration parameters
$SiteURL = "http://portal.crescent.com/projectpipeline/"
$ListName = "Projects"
$FieldName= "TeamMembers"
$UserAccount="Crescent\Omar"

#Get the User Account
$User = Get-SPUser -Identity $UserAccount -Web $SiteURL

#Query to filter List Items which contains user account
$SPQuery = new-object Microsoft.SharePoint.SPQuery
$Query =  "<Where><Eq><FieldRef Name='TeamMembers' LookupId='TRUE'/><Value Type='User'>$($User.ID)</Value></Eq></Where>"
$SPQuery.Query=$Query

#Get site and List objects
$web = Get-SPWeb $SiteURL
$List = $web.Lists.TryGetList($ListName)

#Filter List Items by Query
$ListItems = $List.GetItems($SPQuery)
foreach($item in $ListItems)
{
    write-host $Item["ProjectName"]
}
write-host "Total Number of Projects Found:" $ListItems.Count


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, February 15, 2016

Get Data from SQL Server and Insert Into SharePoint List using PowerShell

Requirement:
We've an External CRM application from which data is to be extracted and inserted into a SharePoint List!

PowerShell to Read Data from SQL Server and Insert into SharePoint:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Sharepoint List
$Web = Get-SPWeb "http://portal.crescent.com/ProjectPipeline/"
$List = $Web.Lists["Projects"]

$ConnectionString = "Server=CrescentSQL;Database=CRM_Integrated_DB;Integrated Security=True"
$SPName = "rpt_Project_pipeline"

#execute the Stored Procedure to Fetch Data
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $ConnectionString
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandType=[System.Data.CommandType]'StoredProcedure'
$sqlCommand.CommandText = $SPName
   
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $sqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$sqlCommand.Dispose()

## Fetch data from Stored Procedure
write-host "Data Import Starts..."
ForEach ($Project in $DataSet.Tables[0]) 
{ 
 #Update Existing Project if the match found
    $SPQuery = New-Object Microsoft.SharePoint.SPQuery 
    $SPQuery.Query = "<Where><Eq><FieldRef Name='ProjectName' /><Value Type='Text'>" + $Project.'Project Name' + "</Value></Eq></Where>"
    $ListItems = $List.GetItems($SPQuery) 
    if($ListItems.Count -gt 0)
    {
        foreach($Item in $ListItems)
        { 
            #update existing Project data
            $Item["Company Type"] = $Project.'Company Type'
            $Item["Sector"] = $Project.'Sector'
            $Item["Country"] = $Project.'Country'
            $Item["Deal Lead"] = $Project.'Deal Lead'
            $Item["Investment Date"] = $Project.'Investment Date'
            $Item["Investment Amount"] = $Project.'Investment Amount'
            $Item["Industry"] = $Project.'Industry'

   try
            {
                $Item.Update() 
            }
            catch
            {
                write-host "Item ID: " $Item["ID"] "not updated"
            }
        }    
    } # Update List Item complete
  
   else #Add New Item incase no match for Project Name
   {
        if([string]::IsNullOrEmpty($Project.'Project Name')){
            #write-host "Project Name is empty and data not updated"
        }
        else{
            $newItem = $List.Items.Add()
            $newItem["Project"] = $Project.'Project Name'
            $newItem["Company Name"] = $Project.'Company Name'
            $newItem["Company Type"] = $Project.'Company Type'
            $newItem["Sector"] = $Project.'Sector'
            $newItem["Country"] = $Project.'Country'
            $newItem["Deal Lead"] = $Project.'Deal Lead'
            $newItem["Investment Date"] = $Project.'Investment Date'
            $newItem["Investment Amount"] = $Project.'Investment Amount'
            $newItem["Industry"] = $Project.'Industry'
       
            TRY
            {
                $newItem.Update()
            }
            CATCH
            {
                Write-Host "Project Name: " $Project.'Project Name' "NOT UPDATED FROM TABLE"
            }
        }
    }
}
write-host "Data Import completed!"



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, February 13, 2016

Create Subscription Settings Service Application in SharePoint using PowerShell

What is subscription settings service application in SharePoint? Well, SharePoint Apps platform relies on Microsoft SharePoint Foundation Subscription Settings service application. So, If you want to use Apps in SharePoint, its a prerequisite to create Subscription Settings service application.

How to create subscription settings service application?
Subscription Settings service application can be created only via PowerShell, There is no GUI to create this Service Application! Lets configure the subscription settings service application by using windows PowerShell.

Don't forget to Change configuration parameters such as the service account, database name,etc on the below script as per your environment. Once done,  run this PowerShell script from either PowerShell ISE or through SharePoint Server 2013 Management Shell.

PowerShell to Create Subscription Settings Service Application in SharePoint
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$ServiceAppName = "Subscription Settings Service Application"
$AppPoolAccount = "Crescent\SP16-AppPool"
$AppPoolName = "Service Application App Pool"
$DatabaseServer = "SP16-SQL001"
$DatabaseName = "SP16_SubsSett_ServiceApp"

Try {
    #Set the Error Action
    $ErrorActionPreference = "Stop"

    #Check if Managed account is registered already
    Write-Host -ForegroundColor Yellow "Checking if Application Pool Accounts already exists"
    $AppPoolAccount = Get-SPManagedAccount -Identity $AppPoolAccount -ErrorAction SilentlyContinue
    if($AppPoolAccount -eq $null)
    {
        Write-Host "Please Enter the password for the Service Account..."
        $AppPoolCredentials = Get-Credential $AppPoolAccount
        $AppPoolAccount = New-SPManagedAccount -Credential $AppPoolCredentials
    }
 
    #Check if the application pool exists already
    Write-Host -ForegroundColor Yellow "Checking if the Application Pool already exists"
    $AppPool = Get-SPServiceApplicationPool -Identity $AppPoolName -ErrorAction SilentlyContinue
    if ($AppPool -eq $null)
    {
        Write-Host -ForegroundColor Green "Creating Application Pool"
        $AppPool = New-SPServiceApplicationPool -Name $AppPoolName -Account $AppPoolAccount
    }
 
    #Check if the Service application exists already
    Write-Host -ForegroundColor Yellow "Checking if Subscription Settings Service Application exists already"
    $ServiceApplication = Get-SPServiceApplication -Name $ServiceAppName -ErrorAction SilentlyContinue
    if ($ServiceApplication -eq $null)
    {
        Write-Host -ForegroundColor Green "Creating Subscription Settings Service Application..."
        $ServiceApplication = New-SPSubscriptionSettingsServiceApplication –ApplicationPool $AppPoolName –Name $ServiceAppName –DatabaseName $DatabaseName –DatabaseServer $DatabaseServer 
        $Proxy = New-SPSubscriptionSettingsServiceApplicationProxy –ServiceApplication $ServiceApplication
        Write-Host -ForegroundColor Green "Subscription Settings Service Application created successfully!"
    }

    #Start service instance 
    $ServiceInstance = Get-SPServiceInstance | Where-Object { $_.TypeName -eq "Microsoft SharePoint Foundation Subscription Settings Service"}
 
    #Check the Service status
    if ($ServiceInstance.Status -ne "Online")
    {
        Write-Host -ForegroundColor Yellow "Starting the Subscription Settings Service Instance..."
        Start-SPServiceInstance $ServiceInstance
    }

}
catch {
    Write-Host $_.Exception.Message -ForegroundColor Red
 }
 finally {
    #Reset the Error Action to Default
    $ErrorActionPreference = "Continue"
 } 
This creates subscription settings service application in SharePoint 2013 and SharePoint 2016.
how to create subscription settings service application using PowerShell
Tags: add subscription settings service application, configure subscription settings service application, configure the subscription settings service application by using windows powershell, create subscription settings service application, create subscription settings service application sharepoint 2013, how to create subscription settings service application, powershell script to create subscription settings service application, provision a subscription settings service application, setting up the subscription settings service application in sharepoint 2013,use powershell to create the subscription settings service application

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Deactivate Feature on All Sites in SharePoint using PowerShell

Requirement: 
After deploying Office web apps with edit licenses in SharePoint, we wanted to disable open in client feature on all site collections in SharePoint. Deactivating a feature is straight forward from SharePoint web UI by getting into:
  • Site Settings >> Site Collection Features
  • Locate the target feature and click on "Deactivate" button next to it.
    sharepoint deactivate feature all sites
However, its cumbersome to repeat this activity for larger number of site collections, isn't it? So, use these PowerShell scripts to deactivate features from all sites in SharePoint!

PowerShell to Disable Feature on All Site Collections in SharePoint
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Feature to Deactivate
$FeatureID="8a4b8de2-6fd8-41e9-923c-c7c3c00f8295"

#Get all Site collections
$SitesColl= Get-SPWebApplication | Get-SPSite -Limit ALL
#Get the Feature
$Feature = Get-SPFeature | Where {$_.ID -eq $FeatureID}

#Check if Feature exists
if($Feature -ne $null)
{
    #Iterate through through each site collection and deactivate the feature
    Foreach($Site in $SitesColl)
    {
 #Check if the Feature is activated
       if (Get-SPFeature -Site $Site.Url | Where {$_.ID -eq $FeatureId})
       {
     #DeActivate the Feature
            Disable-SPFeature –identity $FeatureID -URL $Site.URL -Confirm:$false
            Write-Host "Feature has been Deactivated on "$Site.URL -f Green
 }
        else
        {
            Write-host "Feature was not activated on "$Site.URL -f Cyan
        }
    }
}
else    
{
    Write-Host "Feature doesn't exist:"$FeatureID -f Red
}
This script deactivates the given feature id from all site collections of the entire SharePoint environment.


Deactivate feature on all sites in SharePoint using PowerShell:
How about deactivating a feature on all sites (webs) in SharePoint? Use this PowerShell script to disable a feature for all sites:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Feature to Deactivate
$FeatureID="7201d6a4-a5d3-49a1-8c19-19c4bac6e668"

#Get all webs
$WebsColl= Get-SPWebApplication | Get-SPSite -Limit ALL | Get-SPWeb -Limit ALL

#Get the Feature
$Feature = Get-SPFeature | Where {$_.ID -eq $FeatureID}

#Check if Feature exists
if($Feature -ne $null)
{
    #Iterate through through each site collection and deactivate the feature
    Foreach($web in $WebsColl)
    {
 #Check if the Feature is activated
       if (Get-SPFeature -web $web.Url | Where {$_.ID -eq $FeatureId})
       {
     #DeActivate the Feature
            Disable-SPFeature –identity $FeatureID -URL $web.URL -Confirm:$false
     Write-Host "Feature has been Deactivated on "$web.URL -f Green
        }
        else
        {
            Write-host "Feature was not activated on "$web.URL -f Cyan
        }
    }
}
else    
{
    Write-Host "Feature doesn't exist:"$FeatureID -f Red
} 
This PowerShell script disables the given feature id from all subsites in SharePoint.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Activate Feature on All Sites in SharePoint using PowerShell

In some scenarios, you may have to activate a feature for all sites or site collections in SharePoint. In my case today, I had to activate "Open In Client" feature of SharePoint to disable browser based editing for all site collection in a SharePoint web application.

How to Activate a Feature in SharePoint?
To activate a site collection feature from SharePoint web UI, You can navigate to Site settings >> Site Collection Features >> Click on "Activate" next to the relevant feature.

Pretty straight forward and easy enough through the UI, isn't it? Well, If you have to do the same for a dozens of site collection in the web application, PowerShell is the perfect solution.

Activate feature for all site collections in SharePoint
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$WebAppURL="https://portal.crescent.com/"
$FeatureName="OpenInClient" #Feature Folder Name

#Get all site collections of the web app and iterate through
$SiteColl= Get-SPSite -WebApplication $WebAppURL -Limit ALL
Foreach($Site in $SiteColl)
{ 
 #Get the Feature ID from Feature Name
    $Feature = Get-SPFeature -Site $Site.Url | Where {$_.DisplayName -eq $FeatureName}
 
 #If Feature Found
 if($Feature -ne $null)
    {
  #Check if the Feature is activated already
       if (Get-SPFeature -Site $Site.Url | Where {$_.ID -eq $Feature.Id})
       {
         Write-Host $FeatureName "is already activated at " $Site.Url -f Yellow
        }
       else
       {
   #Activate the Feature
   Enable-SPFeature –identity $Feature.ID -URL $Site.URL
   Write-Host "Feature Activated on "$site.URL -f Green   
  }
 }
 else
 {
  Write-Host "Feature Name doesn't exist:"$FeatureName -f Red
 } 
}
This PowerShell script activates feature on all site collections.

SharePoint: activate feature on all sites
Consider a scenario, where you have to activate a site level (web) feature on 100's of sites under a site collection, which is quite common. In my case, I had to activate Nintex Workflow 2013 feature on all sub sites within a particular site collection.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$SiteCollURL="http://portal.crescent.com/"
$FeatureID="0561d315-d5db-4736-929e-26da142812c5" 

#Get the Feature
$Feature = Get-SPFeature | Where {$_.ID -eq $FeatureID}

#If Feature Found
if($Feature -ne $null)
{     
    #Get all sites under the collection and loop through
    $Site= Get-SPSite $SiteCollURL
    Foreach($Web in $Site.AllWebs)
    {
  #Check if the Feature is activated already
       if (Get-SPFeature -Web $web.Url | Where {$_.ID -eq $FeatureId})
       {
         Write-Host $FeatureID "is already activated at " $Web.Url -f "Blue"
        }
       else
       {
   #Activate the Feature
   Enable-SPFeature –identity $FeatureID -URL $Web.URL
   Write-Host "Feature has been Activated on "$Web.URL -f "Green"
  }
    }
}
else    
{
    Write-Host "Feature doesn't exist:"$FeatureID -f "Red"
}
This script activates feature for all sites in SharePoint.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Friday, February 12, 2016

How to Create a SharePoint 2013 Farm using PowerShell to Avoid GUIDs

Why to use PowerShell to create SharePoint farm rather the nice SharePoint products configuration wizard? Because, SharePoint Products configuration wizard creates Central Administration database names with GUIDs.

Create SharePoint 2013 farm using PowerShell:
How to start? follow these steps to create SharePoint farm using PowerShell.

Step 1: Install SharePoint binaries on each server to your SharePoint 2013 farm. On completion: you will asked to run the "SharePoint Products Configuration Wizard" - Do not run the wizard!

Step 2: Logon to the server where you would like to host SharePoint Central Administration site. Right click on "SharePoint 2013 Management Shell" and select "Run as administrator". The shell will give "Local farm is not accessible", This is expected as we have not created the farm yet. Proceed executing the cmdlets below to create SharePoint 2013 farm using PowerShell.

SharePoint 2013 create new farm using PowerShell
Set the values at #Configuration Settings section below and run these PowerShell commands. Enter the password for Farm account when prompted.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  
 
#Configuration Settings 
$DatabaseServer = "SP13_SQLSrv" 
$ConfigDatabase = "Intranet_Farm_Config" 
$AdminContentDB = "Intranet_Farm_Content_Admin" 
$Passphrase = "2FJlsXghFsas5vdJJKEXXwWF" 
$FarmAccountName = "Crescent\SP13_Farm"   
  
#Get the Farm Account
$FarmAccount = Get-Credential $FarmAccountName
$Passphrase = (ConvertTo-SecureString $Passphrase -AsPlainText -force) 
 
#Create SharePoint Farm
Write-Host "Creating Configuration Database and Central Admin Content Database..." 
New-SPConfigurationDatabase -DatabaseServer $DatabaseServer -DatabaseName $ConfigDatabase -AdministrationContentDatabaseName $AdminContentDB -Passphrase $Passphrase -FarmCredentials $FarmAccount

Write-Host "Installing SharePoint Resources..."
Initialize-SPResourceSecurity

Write-Host "Installing Farm Services ..."
Install-SPService

Write-Host "Installing SharePoint Features..." 
Install-SPFeature -AllExistingFeatures

Write-Host "Creating Central Administration..."               
New-SPCentralAdministration -Port 8080 -WindowsAuthProvider NTLM
 
Write-Host "Installing Help..."
Install-SPHelpCollection -All   

Write-Host "Installing Application Content..." 
Install-SPApplicationContent
 
Write-Host "SharePoint 2013 Farm Created Successfully!"

Step 3: Connect Rest of the Servers to the Farm:
Once you created the SharePoint farm and SharePoint Central Administration site, You can connect rest of the servers to SharePoint Farm either using the PowerShell cmdlet

PowerShell to Add Servers to the SharePoint 2013 Farm:
Close and reopen the SharePoint 2013 Management Shell and run these PowerShell cmdlets.
Connect-SPConfigurationDatabase -DatabaseName "Intranet_Farm_Config" -DatabaseServer "SP13_SQLSrv" -Passphrase (ConvertTo-SecureString "2FJlsXghFsas5vdJJKEXXwWF" -AsPlaintext -Force)

Write-Host "Installing SharePoint Resources..."
Initialize-SPResourceSecurity

Write-Host "Installing Farm Services ..."
Install-SPService

Write-Host "Installing SharePoint Features..." 
Install-SPFeature -AllExistingFeatures

Write-Host "Installing Help..."
Install-SPHelpCollection -All   

Write-Host "Installing Application Content..." 
Install-SPApplicationContent
 
Write-Host "Added the Server to Farm Successfully!" 
Don't forget the change the parameter values according to your environment!

Alternatively, You can use PSConfig wizard and choose "Connect to an existing server farm" and then complete the wizard!
create new SharePoint Farm using PowerShell
This avoids GUIDs in SharePoint Admin database and gives you the flexibility to name your SharePoint Central Admin database.

In case you already created the SharePoint farm using SharePoint 2013 GUI wizard, and you ended up with database names that have GUID’s use this article to remove the GUIDs from SharePoint Central Admin database: How to remove GUID from SharePoint Central Admin Database

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, February 8, 2016

The Complete SharePoint 2013/2010 Site Collection Permission Report using PowerShell

Another PowerShell report generating requirement: SharePoint Site Collection Permission Report to audit permissions on each and every object under a site collection.

This PowerShell script generates a report in HTML format, which consists of
  • Permission setup on All subsites under a site collection
  • Site collection administrators list
  • Permissions applied on each SharePoint object - Such as web (subsite), List, Folder and List Item.
  • Permissions configured for Users, SharePoint Group and AD Groups
  • SharePoint Groups and members of each group who has access.
How to run this script?
How to run the script? Well, Just copy-paste the given script below to either PowerShell ISE or notepad, save it something like "PermissionRpt.ps1", change the configuration variables such as "Site collection URL, Output Report, etc. accordingly. Save and Hit F5!
sharepoint permissions report

SharePoint 2013/2010 permissions report PowerShell
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$HTMLTemplate=@"
<html>
<head>
<!-- Sal - Javascript Function to apply formatting -->
<script type="text/javascript">
function altRows(id){
 if(document.getElementsByTagName){     
  var table = document.getElementById(id);  
  var rows = table.getElementsByTagName("tr");     
  for(i = 0; i < rows.length; i++){          
   if(i % 2 == 0){
    rows[i].className = "evenrowcolor";
   }else{
    rows[i].className = "oddrowcolor";
   }      
  }
 }
}
window.onload=function(){
 altRows('alternatecolor');
}
</script>
 
<!-- CSS Styles for Table TH, TR and TD -->
<style type="text/css">
body{ font-family: Calibri; height: 12pt; }

table.altrowstable {
 border-collapse: collapse; font-family: verdana,arial,sans-serif;
 font-size:11px; color:#333333; border-width: 1px; border-color: #a9c6c9;
 border: b1a0c7 0.5pt solid; /*Sal Table format */  
}
table.altrowstable th {
 border-width: 1px; padding: 5px; background-color:#8064a2;
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; 
 color: white;  font-size: 11pt;  font-weight: 700;  text-decoration: none;
}
table.altrowstable td {
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; color: black; 
 font-size: 11pt; font-weight: 400; text-decoration: none; 
}
.oddrowcolor{ background-color: #e4dfec; }
.evenrowcolor{ background-color:#FFFFFF; }
</style>
</head>
<body>
"@

#Function to get permissions of an object Sal. Such as: Web, List, Folder, ListItem
Function Get-Permissions([Microsoft.SharePoint.SPRoleAssignmentCollection]$RoleAssignmentsCollection, $OutputReport)
{
   foreach($RoleAssignment in $RoleAssignmentsCollection) 
    { 
        #Get the Permissions assigned to Group/User
        $UserPermissions=@()
        foreach ($RoleDefinition in $RoleAssignment.RoleDefinitionBindings)
        {
            #Exclude "Limited Access" - We don't need it sal.
            if($RoleDefinition.Name -ne "Limited Access")
            {
                $UserPermissions += $RoleDefinition.Name +";"
            }    
        }
        
        if($UserPermissions)
        {
            #*** Get  User/Group Name *****#
            $UserGroupName=$RoleAssignment.Member.Name
            $UserName=$RoleAssignment.Member.LoginName  
            #**** Get User/Group Type ***** Is it a User or Group (SharePoint/AD)?
            #Is it a AD Domain Group?
            If($RoleAssignment.Member.IsDomainGroup)
                {
                   $Type="Domain Group"
                }
            #Is it a SharePoint Group?            
            Elseif($RoleAssignment.Member.GetType() -eq [Microsoft.SharePoint.SPGroup])
            {
                 $Type="SharePoint Group"
            }
            #it a SharePoint User Account
            else
            {
                   $Type="User"
            }
            #Send the Data to Report 
            " <tr> <td> $($UserGroupName) </td><td> $($Type) </td><td> $($UserName) </td><td>  $($UserPermissions)</td></tr>" >> $OutputReport
        }
    }
}

Function Generate-PermissionRpt()
{
    Param([Parameter(Mandatory=$true)] [string]$SiteCollectionURL,
          [Parameter(Mandatory=$true)] [string]$OutputReport, 
          [Parameter(Mandatory=$true)] [bool]$ScanFolders, 
          [Parameter(Mandatory=$true)] [bool]$ScanItemLevel) 

    #Try to Get the site collection  
    try
    { 
        $Site = Get-SPSite $SiteCollectionURL -ErrorAction SilentlyContinue
    }
    catch
    { 
        write-host Site Collection with URL:$SiteCollectionURL Does not Exists!
        return
    }   
    
    #Append the HTML File with CSS into the Output report
    $Content = $HTMLTemplate > $OutputReport
   
    "<h2> Site Collection Permission Report: $($Site.RootWeb.Title) </h2>" >> $OutputReport 
   
    #Table of Contents
    "<h3> List of Sites</h3> <table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr><th>Site Name </th><th> URL </th><th> Permission Setup </th></tr>" >> $OutputReport
    #Get Users of All Webs : Loop throuh all Sub Sites
    foreach($Web in $Site.AllWebs) 
    {
        
        if($Web.HasUniqueRoleAssignments -eq $true)
        {
            $PermissionSetup ="Unique Permissions"
        }
        else
        {
            $PermissionSetup="Inheriting from Parent"
        }
        
        "<tr> <td> <a href='#$($web.Title.ToLower())'>$($web.Title)</a> </td><td> $($web.URL)</td> <td> $($PermissionSetup)</td></tr>" >> $OutputReport
    }
   
    #Site Collection Administrators Heading 
    "</table><br/><b>Site Collection Administrators</b>" >> $OutputReport
     "<table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
 
    #Write Table Header
    "<th>User Account ID </th> <th>User Name </th></tr>" >> $OutputReport 
   
    #Get All Site Collection Administrators
    $Site.RootWeb.SiteAdministrators | sort $_.Name | ForEach-Object {  
    "<tr><td> $($_.LoginName) </td> <td> $($_.Name)</td></tr> " >> $OutputReport
    }

    $Counter=0;
    #Get Users of All Webs : Loop throuh all Sub Sites
    foreach($Web in $Site.AllWebs)
    { 
        Write-Progress -Activity "Collecting permissions data. Please wait..." -status "Processing Web: $($Web.URL)" -percentComplete ($Counter/$Site.AllWebs.count*100)
    
        #Check if site is using Unique Permissions or Inheriting from its Parent Site!
        if($Web.HasUniqueRoleAssignments -eq $true)
        {
            "</table><br/><hr> <h3>Site: <a name='$($Web.Title.ToLower())' href='$($web.URL)' target='_blank'>$($Web.Title)</a> is using Unique Permissions. </h3>" >> $OutputReport
        }
        else
        {
            "</table><br/><hr> <h3>Site: <a name='$($Web.Title.ToLower())' href='$($web.URL)' target='_blank'>$($Web.Title)</a> is Inheriting Permissions from its Parent Site.</h3>" >> $OutputReport
        }
   
        #Get the Users & Groups from site which has unique permissions - TOP sites always with Unique Permissions
        if($Web.HasUniqueRoleAssignments -eq $True)
        {        
            Write-host Processing Web $Web.URL
            #*** Get all the users granted permissions DIRECTLY to the site ***
            "<b>Site Permissions</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
            "<th>Users/Groups </th> <th> Type </th><th> User Name </th> <th>Permissions</th></tr>" >> $OutputReport

            #Call the function to get Permissions Applied 
            Get-Permissions $Web.RoleAssignments $OutputReport
        
              
            #****** Get Members of Each Group at Web Level *********#
            "</table></br> " >>$OutputReport
            
            #Check if any SharePoint Groups Exists, if yes, Get members of it
            $WebGroupRoleAssignments = $Web.RoleAssignments | Where { $_.Member.GetType() -eq [Microsoft.SharePoint.SPGroup]}
            if($WebGroupRoleAssignments)
            {
                "<b>Group Users</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >>$OutputReport
                foreach($WebRoleAssignment in $WebGroupRoleAssignments) 
                { 
                    "<th colspan='3'><b>Group:</b> $($WebRoleAssignment.Member.Name)</th></tr> " >> $OutputReport 
                    foreach($user in $WebRoleAssignment.member.users)
                    {
                        #Send the Data to Log file
                        " <tr> <td> $($user.Name) </td><td> $($user.LoginName) </td><td> $($user.Email)</td><tr>" >> $OutputReport
                    }
                }
            }
        } #Web.HasUniqueRoleAssignments Over      
      
     #********  Check All List's Permissions ********/
        foreach($List in $Web.lists)
        {
            #Skip the Hidden Lists
            if( ($List.HasUniqueRoleAssignments -eq $True) -and  ($List.Hidden -eq $false))
            {
                "</table><br/><b>List: [ $($List.Title) ] at <a href='$($List.ParentWeb.Url)/$($List.RootFolder.Url)'>$($List.ParentWeb.Url)/$($List.RootFolder.Url)</a> is using Unique Permissions.</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
                "<th>Users/Groups </th><th> Type </th><th> User Name </th><th> Permissions</th></tr>" >> $OutputReport 
                   
                #Call the function to get Permissions Applied 
                Get-Permissions $List.RoleAssignments $OutputReport
            }
            "</table>" >>$OutputReport
            
            #********  Check Folders with Unique Permissions ********/
            if($ScanFolders -eq $True) 
            {
                $UniqueFolders = $List.Folders | where { $_.HasUniqueRoleAssignments -eq $True }     
                #Check if any folder has Unique Permission
                if($UniqueFolders) 
                {
                    #Get Folder permissions
                    foreach($folder in $UniqueFolders)
                    {
                        #Write Table Headers
                        $FolderURL=$folder.ParentList.ParentWeb.URL/$folder.Url
                        "<br/><b>Folder: <a href='$($FolderURL)' target='_blank'>$($Folder.Title)</a> is using Unique Permissions.</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
                        "<th>Users/Groups </th><th> Type </th><th> User Name </th><th> Permissions</th></tr>" >> $OutputReport 

                        #Call the function to get Permissions Applied 
                        Get-Permissions $folder.RoleAssignments $OutputReport 
                        
                        "</table>" >>$OutputReport
                    }
                }
            }
            
            #********  Check Items with Unique Permissions ********/
            if($ScanItemLevel -eq $True) 
            {
                $UniqueItems = $List.Items  | where { $_.HasUniqueRoleAssignments -eq $True }     
                #Check if any Item has Unique Permission Sal
                if($UniqueItems) 
                {
                    #Get Folder permissions
                    foreach($Item in $UniqueItems)
                    {
                        #Get Item's Name if Title is NULL
                        if($Item.Title -ne $null) {$ItemTitle = $Item.Title } else {$ItemTitle= $Item["Name"] }
                        #Write Table Headers
                        $ItemURL= $item.ParentList.ParentWeb.Site.MakeFullUrl($item.ParentList.DefaultDisplayFormUrl)
                        "<br/><b>Item: <a target='_blank' href='$($ItemURL)?ID=$($Item.ID)'>$($ItemTitle)</a> in list/library <a href='$($List.ParentWeb.Url)/$($List.RootFolder.Url)'>$($List.Title) </a> is using Unique Permissions.</b><table class='altrowstable' id='alternatecolor' cellpadding='5px'><tr>" >> $OutputReport
                        "<th>Users/Groups </th><th> Type </th><th> User Name </th><th> Permissions</th></tr>" >> $OutputReport 

                        #Call the function to get Permissions Applied 
                        Get-Permissions $Item.RoleAssignments $OutputReport 
                        
                        "</table>" >>$OutputReport
                    }
                }
            }
        } #List
    $Counter=$Counter+1;        
    } #Web
"</body></html>" >>$OutputReport
Write-host "`n Permission report generated successfully at "$OutputReport 

}

#**********Configuration Variables************
$OutputReport = "C:\PermissionRpt.htm"
$SiteCollURL="http://portal.crescent.com"
$ScanFolders=$False
$ScanItemLevel=$False

#Call the function to Get Permissions Report
Generate-PermissionRpt $SiteCollURL $OutputReport $ScanFolders $ScanItemLevel

Output:
Script is included with a progress bar indicator. Once execution completed, it generates a HTM file at given location.
You can optionally turn on Folders & Item Level flags to include them in permission reporting. If so, You can expect some delays in script execution!

Here is a sample site collection permission report this script generated! 
sharepoint site collection permission report
This script doesn't count "Limited Permissions"!


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


You might also like:

Related Posts Plugin for WordPress, Blogger...