Sunday, September 25, 2016

Get User Department, Job Title from User Information List of SharePoint using PowerShell

Requirement: From a business requirement, need to generate a report for department wise users of a Intranet portal site. Say, How many users from "Sales" department have access to SharePoint Intranet portal?

Solution: Either query user profiles store or user information list to get the user profile properties such as Department, Job title, etc.


PowerShell to query User Information List in SharePoint:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters  
$WebURL="http://portal.crescent.com/"
$ReportLocation = "C:\UserAnalysisRpt.csv"

#Get the Web
$Web = Get-SPWeb $WebURL

#Get User information list
$UserInfoList = $Web.SiteUserInfoList

 #Get all Groups of the web and Iterate through    
 Foreach ($Group in $Web.Groups) 
 {
    #Get Permission Levels Applied to the Group   
    $RoleAssignment = $Web.RoleAssignments.GetAssignmentByPrincipal($Group)
 
    $RoleDefinitionNames=""
    foreach ($RoleDefinition in $RoleAssignment.RoleDefinitionBindings)
    {  
        $RoleDefinitionNames+=$RoleDefinition.Name+";"
    }
    #Array to Hold Result - PSObjects
    $ResultCollection = @()

    "Group Name: $($Group.name) : Permissions: $($RoleDefinitionNames)" >> $ReportLocation
    #Iterate through Each User in the group
        foreach ($User in $Group.users) 
        {
            #Get the User details from UIL
            $UserInfo = $UserInfoList.GetItemById($User.ID)            
            $Department = $UserInfo['Department']
            $JobTitle = $UserInfo["JobTitle"] 
            
            #Send the output the report file
            $User.name + "`t" + $user.Email + "`t" + $Department + "`t" + $JobTitle >> $ReportLocation
        }    
 } 
 Write-host "User analysis data has been Exported to $ReportLocation"

If you want to go for querying user profile properties from profile store, use my another post: Query User Profile Properties using PowerShell in SharePoint 2013

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


Saturday, September 24, 2016

How to Add Administrator & Grant Permission to Service Application using PowerShell

Often there are situations when you may need to add administrators or grant permissions to SharePoint service applications. Say, the setup account-farm account isn't added as service application administrator or not having permission to the service application.

How to Add Service Application Administrator in SharePoint 2016?
To add a  Service Application administrator, navigate to:
  • SharePoint 2016 Central Admin >> Manage Service Applications
  • From the list of available Service Applications, select the target service application such as "Managed Metadata service application".sharepoint 2016 service application add administrator grant permission
  • In the Ribbon click on "Administrators" button. Enter the user name that you wish to have admin rights to SharePoint service application >> Click on "Add" button.
  • From the permissions section, select "Full Control". Commit your changes by clicking the OK button.
    powershell to add service application administrator in SharePoint 2016
  • Similarly, to add permission, Click on "Permissions" button from the ribbon, Enter the user and add appropriate permission to the user.
    How to grant permission to service application using PowerShell

When these things are repeated, Lets use PowerShell to add administrator and grant permissions to service applications, say: Managed Metadata Service Application.

PowerShell to Add Service Application Administrator 
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Variables
$ServiceAppName="Managed Metadata Service Application"
$UserAccount="Crescent\Salaudeen"
$AccessRights = "Full Control"

#Get the service application
$ServiceApp = Get-SPServiceApplication -Name $ServiceAppName
#Convert user account to claims
$UserPrincipal = New-SPClaimsPrincipal -Identity $UserAccount -IdentityType WindowsSamAccountName

#Get the Service Application Security collection
$ServiceAppSecurity = Get-SPServiceApplicationSecurity $ServiceApp -Admin
#Add user & rights to the collection
Grant-SPObjectSecurity $ServiceAppSecurity -Principal $UserPrincipal -Rights $AccessRights

#Apply the Security changes
Set-SPServiceApplicationSecurity $ServiceApp $ServiceAppSecurity -Admin 

Add Permission to Service Application using PowerShell
How about granting permission to service application? Well, the similar code goes for providing permission to service application, except the "-Admin" switch. Here is an example:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Variables
$ServiceAppName="Managed Metadata Service Application"
$UserAccount="Crescent\Salaudeen"
$AccessRights = "Full Access to Term Store"

#Get the service application
$ServiceApp = Get-SPServiceApplication -Name $ServiceAppName
#Convert user account to claims
$UserPrincipal = New-SPClaimsPrincipal -Identity $UserAccount -IdentityType WindowsSamAccountName

#Get the Service Application Security collection
$ServiceAppSecurity = Get-SPServiceApplicationSecurity $ServiceApp
#Add user & rights to the collection
Grant-SPObjectSecurity $ServiceAppSecurity -Principal $UserPrincipal -Rights $AccessRights

#Apply the Security changes
Set-SPServiceApplicationSecurity $ServiceApp $ServiceAppSecurity

Add User Profile Service Application Administrator and Grant Permissions: 
Lets combine both the scripts to add administrator and set permission to user profile service application in SharePoint 2016 to avoid "No User Profile Application available to service the request. Contact your farm administrator." error.  
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Variables
$UserAccount="Crescent\Salaudeen"
$AccessRights = "Full Control"

#Convert user account to claims
$UserPrincipal = New-SPClaimsPrincipal -Identity $UserAccount -IdentityType WindowsSamAccountName

#Get the user profile service application
$ServiceApp =  Get-SPServiceApplication | ? { $_.TypeName -eq "User Profile Service Application" }

Write-host "Adding Administrator to User Profile Service Application..."
#Get the Service Application Administrators Security collection
$ServiceAppAdmins = Get-SPServiceApplicationSecurity $ServiceApp -Admin
#Add user to the collection
Grant-SPObjectSecurity $ServiceAppAdmins -Principal $UserPrincipal -Rights $AccessRights
#Apply the new Security to service application
Set-SPServiceApplicationSecurity $ServiceApp $ServiceAppAdmins -Admin

Write-host "Granting permission to User Profile Service Application..."
#Get the Service Application Permissions Security collection
$ServiceAppPermission = Get-SPServiceApplicationSecurity $ServiceApp
#Add user & rights to the collection
Grant-SPObjectSecurity $ServiceAppPermission -Principal $UserPrincipal -Rights $AccessRights
#Apply the Security changes
Set-SPServiceApplicationSecurity $ServiceApp $ServiceAppPermission


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


Thursday, September 15, 2016

An error occurred while getting information about the user at server : The RPC server is unavailable

Problem:
After installing SharePoint 2016 to setup a new farm, tried running SharePoint 2016 products configuration wizard, and the wizard failed at step 3 saying:
Failed to create the configuration database.
An Exception of type System.InvalidOperationException was thrown. An error occurred while getting information about the user <Farm-Admin> at server <Domain-Name>: The RPC server is unavailable
Failed to create the configuration database. Additional exception information: An error occurred while getting information about the user account at server domain: The RPC server is unavailable
Tried creating SharePoint farm using PowerShell, but got the same results.

Troubleshooting:
  • Verified that the SharePoint server is part of the domain and there is no connectivity, network, firewall related issues.
  • Verified that the setup account is granted with DB_Creator & Security_Admin server roles. 
  • Tried Adding the IP of the DC to HOST file of the Local server
  • Tried ipconfig /flushdns - and ipconfig /registerdns commands as per Technet forums, but didn't help!
Solution: Download and install the KB KB3115299 to fix this issue!

As a general recommendation, download and install all latest patches before you create SharePoint 2016 farm. Download latest patches from Microsoft Technet site:
https://technet.microsoft.com/en-us/library/mt715807(v=office.16).aspx#BKMK_2016



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


Tuesday, September 6, 2016

How to Change Default Content Access (Crawl) Account in SharePoint 2016 Search

Once we complete creating Search Service Application, The next step is to configure default content access account, which is used by Crawl Component to crawl the content sources like SharePoint content. This crawl account will have read access to all your search content sources such as SharePoint Web Applications.

You can change search crawl account from SharePoint Central Administration as:
  • SharePoint Central Admin >> Application Management >> Manage Service applications
  • From the service applications list, Select the Search service application
  • In the Search Administration page, click on the account already configured for Default content access account.
  • Provide the new crawl account user name and password.
    How to Change Default Content Access Crawl Account in SharePoint 2016 Search

PowerShell to Change Search Crawl Account in SharePoint 2016:
If you want to change the default content access account for SharePoint 2013 search service application using PowerShell, use this script:

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Set Default content access account
$AccountID = "Crescent\SP16_Crawl"
$Password = Read-Host -AsSecureString  

#Get Search service application
$SearchInstance = Get-SPEnterpriseSearchServiceApplication

#Set default content access account for crawl
Set-SPEnterpriseSearchServiceApplication -Identity $SearchInstance -DefaultContentAccessAccountName $AccountID -DefaultContentAccessAccountPassword $Password
As soon as you change Search Crawling Account, SharePoint automatically creates a web application policy with "Full Read" permissions on all Web applications!

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


How to Test Outgoing Emails in SharePoint using PowerShell?

On troubleshooting SharePoint Email issues, as a first step, we have to check outgoing Email settings applied on SharePoint central Administration site are valid. So, how to test SharePoint outgoing email quickly? Here are my PowerShell scripts to test outgoing email in SharePoint 2013.

Method 1: Send Email using SPUtility's SendEmail
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$SiteURL="http://portal.crescent.com/ "
$Email = "salaudeen.rajack@crescent.com"
$Subject = "Test Email from SharePoint"
$Body = "Test Email Body"

#Get the Web 
$Web = Get-SPWeb $SiteURL

#Send Email using SPUtility SendEmail method
[Microsoft.SharePoint.Utilities.SPUtility]::SendEmail($Web ,0,0,$Email,$Subject,$Body)
The above PowerShell script sends Email to the given Email and returns "true" if success.
how to test sharepoint outgoing email

Method 2: Using .Net SMTP Send Mail
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Parameters
$EmailTo = "salaudeen.rajack@crescent.com"
$Subject = "Test Email from SharePoint"
$Body = "Test Email Body"

#Get the outgoing Email Server settings
$SPGlobalAdmin = New-Object Microsoft.SharePoint.Administration.SPGlobalAdmin
$SMTPServer = $SPGlobalAdmin.OutboundSmtpServer
$EmailFrom = $SPGlobalAdmin.MailFromAddress

#Frame Email Message
$Message = new-object Net.Mail.MailMessage
$SMTP = new-object Net.Mail.SmtpClient($SMTPServer)
$Message.From = $EmailFrom
$Message.To.Add($EmailTo)
$Message.subject = $Subject
$Message.body = $Body

#Send the Email
$SMTP.Send($Message)

Method 3: Using PowerShell 3.0 Send-Mail Message
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Parameters
$EmailTo = "salaudeen.rajack@crescent.com"
$EmailSubject = "Test Email from SharePoint"
$EmailBody = "Test Email Body"

#Get the outgoing Email Server settings
$SPGlobalAdmin = New-Object Microsoft.SharePoint.Administration.SPGlobalAdmin
$SMTPServer = $SPGlobalAdmin.OutboundSmtpServer
$EmailFrom = $SPGlobalAdmin.MailFromAddress

#Using PowerShell 3.0 Send-Mail Message:
Send-MailMessage -To $EmailTo -From $EmailFrom -Subject $EmailSubject -Body $EmailBody -BodyAsHtml -SmtpServer $SmtpServer -UseSsl
Last but not least, We may have to make sure exchange server or SMTP accepts Emails from SharePoint servers

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


Change My Site Host URL in User Profile Service Application using PowerShell

Once My site host web application and site collection is created, the next step is to configure user profile service application to use the My site host. Here is how to set my site host in user profile service application of SharePoint 2016.
  • Login to SharePoint 2016 Central Administration site
  • Click on Application Management >> Manage service applications.
  • Select your User Profile Service Application >> From the Manage User profile service page, under My Site Settings section, click on "Setup My Sites" link.
    sharepoint 2016 set my site host url powershell
  • Enter the My Site Host URL and click Save. You may have to specify other settings such as Personal Site Location, Site Naming Format, Secondary Owner, etc.
Make sure your My Site URL is the default one under AAM!

PowerShell to Set My Site Host Location in SharePoint: 
Lets change my site host URL in SharePoint 2013 using PowerShell
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$MySiteHostURL="http://mysite.crescent.com/ "

#$MySite = Get-SPSite $MySiteHostURL
$SiteContext = Get-SPServiceContext($MySiteHostURL)
$UPM = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($SiteContext)
#remove my site host location
$UPM.MySiteHostUrl = ""
#Change My Site Host location
$UPM.MySiteHostUrl = $MySiteHostURL
Write-host "My Site Host Location Changed!"

Tags: sharepoint change my site host url, sharepoint 2010 change my site host url powershell, sharepoint 2013 change my site host, sharepoint 2013 change my site host location, change my site host url sharepoint 2013 powershell, sharepoint 2013 my site host url

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


Create My Site Host Site Collection for SharePoint 2016 using PowerShell

Creating My Site Host in SharePoint involves following steps:
  1. Create a new web application for My Site
  2. Create "Personal" wildcard Managed Path for the web application
  3. Enable Self-Service Site Creation for the web app
  4. Create a root site collection using My Site Host template
  5. In User Profile Service Application, configure My Site Host location

PowerShell script to create Web application and My site Host Site Collection for SharePoint 2016:

Step 1: Create a Web application for My Site:
#PowerShell to create a web application
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Define Variables for Web Application Creation
$WebAppName = "Crescent Mysite"
$HostHeader = "mysite.Crescent.com"
$WebAppURL="http://" + $HostHeader
$WebAppPort = "80"
$ContentDBName = "Crescent_MySite_Content"
$AppPoolName = "MySite Web Application AppPool"
$AppPoolAccount = "Crescent\SP16_AppPool"

#Authentication Provider
$AuthProvider = New-SPAuthenticationProvider 

#Check if Managed account is registered already
Write-Host -ForegroundColor Yellow "Checking if the Managed Accounts already exists"
$AppPoolAccount = Get-SPManagedAccount -Identity $AppPoolAccount -ErrorAction SilentlyContinue
if($AppPoolAccount -eq $null)
{
    Write-Host "Please Enter the password for the App Pool Account..."
    $AppPoolCredentials = Get-Credential $AppPoolAccount
    $AppPoolAccount = New-SPManagedAccount -Credential $AppPoolCredentials
}

#Create new Web Application
New-SPWebApplication -name $WebAppName -port $WebAppPort -hostheader $HostHeader -URL $WebAppURL -ApplicationPool $AppPoolName -ApplicationPoolAccount (Get-SPManagedAccount $AppPoolAccount) -AuthenticationMethod NTLM -AuthenticationProvider $AuthProvider -DatabaseName $ContentDBName

Step 2: Create "Personal" Managed Path:
#Create Managed Path for My Site Web application
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Define Parameters 
$WebAppURL = "http://mysite.Crescent.com"
$WebApp = Get-SPWebApplication $WebAppURL

#Add Managed Path
New-SPManagedPath "Personal" -WebApplication $WebAppURL -Explicit 


Step 3: Enable Self Service Site Creation for My Site Web Application:
#Enable Self service site creation
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Set Parameters 
$WebAppURL = "http://mysite.Crescent.com"
$WebApp = Get-SPWebApplication $WebAppURL

#Enable Self Service Site collection
$webApp.SelfServiceSiteCreationEnabled = $true
$webApp.RequireContactForSelfServiceSiteCreation = $false
$webApp.Update()


Step 4: Create My Site Host Site Collection:
#Create My Site Host Site collection
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Set Parameters for My Site Host Site Collection Creation
$WebAppURL = "http://mysite.Crescent.com"
$FarmAdminAccount="Crescent\SP16_Farm"
$Contentdatabase="Crescent_MySite_Content"
$WebApp = Get-SPWebApplication $WebAppURL

#Create My Site Host site collection
New-SPSite -Name "My Site Host" -Url $WebAppURL -Template "SPSMSITEHOST#0" -OwnerAlias $FarmAdminAccount -ContentDatabase $Contentdatabase
Once all these steps are completed, make sure you set My Site Host location in your user profile service application!

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


Sunday, September 4, 2016

How to Hide the Timeline in the Task List of SharePoint 2013/2016

Requirement: Hide timeline from SharePoint 2016 task list
sharepoint task list hide timeline
Solution: Here is how you can hide the timeline in SharePoint 2013/2016 task lists.
  1. Go to the Task List, Click on List Tab and then click on "Modify View" button from the ribbon.
  2. In the Edit view page, scroll down and expand the 'Style' section.
  3. Under the Style selection, Uncheck the checkbox 'Show timeline above the view'
  4. click on 'OK' button to save changes.
Here is the Result:
sharepoint 2013 task list hide timeline

Alternatively, You can edit the page, Edit List view properties, and uncheck "Show Timeline" Checkbox to hide timeline from SharePoint tasks list.

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


Thursday, September 1, 2016

Backup-Restore All SharePoint Databases in SQL Server using PowerShell

Requirement: We planned to move our SharePoint's SQL database to a different server. So, we had to backup and restore all SharePoint databases from one SQL Server 2012 instance to another.

Solution:
Here are the steps at high level to move SharePoint from one SQL Server to another:
  1. Get a list of all SharePoint databases
  2. Backup all SharePoint databases from the source SQL Server
  3. Restore the backups to the target SQL server instance
  4. Change the SQL Alias in SharePoint Server to point to the new server.
Step 1: Get all SharePoint databases
#Get All SharePoint Databases and form an Array
Get-SPDatabase | Select -ExpandProperty Name | foreach { $Databases+= $_+"," }

#Export to a txt file
$Databases > databases.txt
This script gets you a list of all SharePoint databases, including configuration database, content databases and service application databases to a comma separated string. Lets take that string and give it to our backup script.
Tips: If you want to get all Content databases, use: Get-SPContentDatabase | Select -Expandproperty Name

Step 2: Backup All SharePoint Databases:
Before taking backup of all SharePoint databases, make sure you set the site(s) to Read-only mode (or set the database to Read-only in SQL Server) in order to avoid any data inconsistency issues.

Here is the PowerShell script to backup all Databases from SQL Server:
#Import SQL PowerShell module
Import-Module SQLPS –DisableNameChecking

#Configuration parameters
$SQLInstance="Cre-SQL-001"
$BackupPath="F:\Backup\"

#Array of Database names, Comma separated
$DatabaseColl="StateService_d73ac2ad313a41ba83d457bfb78cb4f3,Secure_Store_Service_DB_c13a71870bad4decb6f7582d9e26f46a,WordAutomationServices_e290fc12c8ee4ce09c38ba7fc8f089c9,Social DB,Bdc_Service_DB_f937be05fe5f48158d4895b6f2bb9674,WebAnalyticsServiceApplication_StagingDB_9efa01f9-ce98-4e4e-9e2d-ecfc9e19fc79,SharePoint_Config_2010,PerformancePoint Service Application_c1a8062656434a24a9c53f05fc8144f7,Search_Service_Application_CrawlStoreDB_76166d368e4a4ef58aabf8df57d7c670,Search_Service_Application_PropertyStoreDB_1fc75775ee894fd2aa0c4d1e7c75f0c7,Application_Registry_Service_DB_8e93a305ea904c81a34a3dd205265fc7,SharePoint_AdminContent_e0021dbb-cc00-4b58-a16d-9c4c481228ff,WSS_Content,WSS_Content_KM,WSS_Content_MySites,Search_Service_Application_DB_bfdf7354e7f84b1281121c7bdca7b43f,Managed Metadata Service_ff52c4f0cd504d938cd1b3dc9ef9ae8b,Profile DB,WSS_Logging,Sync DB,WebAnalyticsServiceApplication_ReportingDB_894b25ce-a2ba-4c7c-885e-08b3924deec8"

#Convert comma separated into an Array
$DatabaseNames = $DatabaseColl -split ","

#Backup Each Database
Foreach($DBName in $DatabaseNames)
{
  write-host "Backing up the database: $($DBName)"
  Backup-SqlDatabase -ServerInstance $SQLInstance -Database $DBName -BackupFile "$($BackupPath)\$($DBName).bak"
}

Step 3: Restore all databases to the target SQL Server instance
Copy all backup files from the source server to the target and then, use the below PowerShell script to restore SQL Server databases in bulk. Make sure the original location (as in the source database server) do exists in the target server.

PowerShell script to restore SQL Server databases in bulk:
#Import SQL PowerShell module
Import-Module SQLPS –DisableNameChecking

#Configuration parameters
$SQLInstanceName="Cre-SQL-002"
$BackupPath="E:\Backup\"

#Get all Backup files
$BackupFilesColl = Get-ChildItem –Path $BackupPath | where {$_.extension -eq ".bak"}

#Iterate through each backup file
Foreach($BackupFile in $BackupFilesColl)
{
 #Get the full path of the backup file
        $DatabasePath = $BackupPath + '\' + $BackupFile

 #Frame the Database Name
        $DatabaseName = $BackupFile.ToString().Replace(".bak", "")

 #Restore the database
 write-host "Restoring Database: $($DatabaseName)"
 Restore-SqlDatabase -ServerInstance $SQLInstanceName -Database $DatabaseName -BackupFile $DatabasePath
}
Backup-Restore All SharePoint Databases in SQL Server using PowerShell

Step 4: Change SQL Server Alias to Point your New SQL Server:
and as a final step, change your SQL Alias from SharePoint servers to point new SQL server instance. Here is my post on configuring SQL server alias: How to Create SQL Server Alias for SharePoint. Do IISReset once done!

These scripts can be used in scenarios, such as:
  • During SharePoint migrations, where you may be taking all SharePoint databases to a new SQL Server instance.
  • When you have to move SharePoint databases from a Single standalone SQL Server to a highly available cluster, or SQL Server AlwaysOn cluster.


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...