Set Permissions on Multiple Sites using PowerShell

These days I had a request to add an Active Directory group with Contributor rights on a SharePoint Site Collection. Since many sites had broken inheritance, using the UI was not an option so I created a small PowerShell Script that enumerates all Webs and if the Inheritance is broken, it adds the group with the specified Role.

Notes:

• The If command uses the $web.Url.Contains directive in order to modify the rights only on a subset of sites. If all Webs have to be crawled, use if ($web.HasUniquePerm -and $web.RequestAccessEnabled) instead.
• This script modifies permissions only on webs. Lists and Items with unique permission will not be touched.

if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell
}


$site = Get-SPSite -Identity "http://spdev/sites/SiteCollection"


foreach($web in $site.AllWebs)
    {

    if ($web.HasUniquePerm -and $web.RequestAccessEnabled -and ($web.Url.Contains("/SiteCollection/BU1") -or $web.Url.Contains("/SiteCollection/BU2")))
        {
            $account = $web.EnsureUser("Domain\QATeam")
            $role = $web.RoleDefinitions["Contribute"]

            $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
            $assignment.RoleDefinitionBindings.Add($role)

            $web.RoleAssignments.Add($assignment)
        }
    $web.Dispose()
    }
$site.Dispose()

References

• http://social.technet.microsoft.com/Forums/sharepoint/en-US/4373cef1-2471-4199-aa16-784684fc82de/adding-permission-to-list-using-powershell

SharePoint Start Workflow on All Items of a List via PowerShell

To start a List Workflow in SharePoint on All Items of a list is a pain through GUI but it's a piece of cake in PowerShell. Note that the Workflow will not start instantly (as opposed to triggering through the UI) but in maximum 5 minutes because it's started through the SharePoint Timer Service.

#updated on 10/17/2013

# URL of the Site
$web = Get-SPWeb -Identity "https://sharepointsrv/site1"

$manager = $web.Site.WorkFlowManager

# Name of the list
$list = $web.Lists["Shared Documents"]

# Name of the Workflow
$assoc = $list.WorkflowAssociations.GetAssociationByName("On Item Created","en-US")

$data = $assoc.AssociationData
$items = $list.Items
foreach($item in $items)
 {
 $wf = $manager.StartWorkFlow($item,$assoc,$data,$true)
 }
 
$manager.Dispose()
$web.Dispose()
#

References

• http://social.technet.microsoft.com/Forums/sharepoint/en-US/dffdceab-f2fe-4886-8e2a-ea26cf08c78a/how-to-start-a-speific-workflow-programatically-in-sharepoint-2010-via-powershell

Find SharePoint Web Template Name using PowerShell

You can find out the Template Name and Id of a SharePoint Site using the following PowerShell command:

$web =  Get-SPWeb -Identity "http://spsrv/sites/web1/"
Write-Host $web.WebTemplate "," $web.WebTemplateId

To list all Template Names and IDs:

Get-SPWebTemplate | select ID, Name, Title | Sort-Object ID

References:

• http://blogs.technet.com/b/praveenh/archive/2013/04/04/get-a-list-of-web-templates-and-ids-in-a-sharepoint-site.aspx
• http://social.technet.microsoft.com/Forums/sharepoint/en-US/07cf57c3-8965-4f85-8911-2c95aa78fed5/how-to-get-the-name-of-the-template-in-sharepoint-2010-using-powershell

Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob - Object reference not set to an instance of an object.

Problem

The Event Log of the SharePoint Server is reddish due to events occurring every 5 minutes:

Event Log Error:

Log Name:      Application

Source:        Microsoft-SharePoint Products-SharePoint Foundation

Date:          2/18/2013 7:00:01 PM

Event ID:      6398

Task Category: Timer

Level:         Critical

Keywords:     

Description:

The Execute method of job definition Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob (ID 8ea0b71c-08c4-4644-a0bc-c361c391bc45) threw an exception. More information is included below.

Object reference not set to an instance of an object.

Event Xml:

 

   

    6398

    14

    1

    12

    0

    0x4000000000000000

   

    355489

   

   

    Application

 

 

    Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob

    8ea0b71c-08c4-4644-a0bc-c361c391bc45

    Object reference not set to an instance of an object.

 

ULS Log:

3880        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Monitoring                      nasq  Medium    Entering monitored scope (Timer Job OfficeWebApplicationsCacheCreationJob)  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3881        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Office Viewing Architecture     cu85  Medium    Cache Creation Job Starting [JobId: 8ea0b71c-08c4-4644-a0bc-c361c391bc45]  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3882        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Office Viewing Architecture     cu86  Exception  Cache Creation Job encountered an exception [JobId: 8ea0b71c-08c4-4644-a0bc-c361c391bc45] System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)     at Microsoft.SharePoint.SPSite.GetSPRequest()     at Microsoft.SharePoint.SPSite.get_Request()     at Microsoft.SharePoint.SPSite.get_Usage()     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp, Boolean elevateReturn, String ownerLogin, String ownerName, String ownerEmail)     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp)     at Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob.Execute(SPJo...  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3883        02/18/2013 19:05:00.04*  OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Office Viewing Architecture     cu86  Exception  ...bState jobState)  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3884        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Unified Logging Service         c91s  Monitorable  Watson bucket parameters: Office Web Apps, ULSException14, 52a97675 "office web apps", 0e00178d "14.0.6029.0", 1f65804a "microsoft.sharepoint", 0e0017ee "14.0.6126.0", 5021a68d "wed aug 08 02:36:45 2012", 000057b1 "000057b1", 01ab21e4 "01ab21e4", 4a6d3421 "nullreferenceexception", 63753836 "cu86"  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3885        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Timer                           6398  Critical  The Execute method of job definition Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob (ID 8ea0b71c-08c4-4644-a0bc-c361c391bc45) threw an exception. More information is included below.  Object reference not set to an instance of an object.  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3886        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Timer                           72ae  Unexpected  Exception stack trace:    at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)     at Microsoft.SharePoint.SPSite.GetSPRequest()     at Microsoft.SharePoint.SPSite.get_Request()     at Microsoft.SharePoint.SPSite.get_Usage()     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp, Boolean elevateReturn, String ownerLogin, String ownerName, String ownerEmail)     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp)     at Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob.Execute(SPJobState jobState)     at Microsoft.SharePoint.Administration.SPTimerJobInvokeInternal.Invoke(SPJobDefinition jd, Guid targetInstanceId, Boolean isTimerSer...  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3887        02/18/2013 19:05:00.04*  OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Timer                           72ae  Unexpected  ...vice, Int32& result)  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3891        02/18/2013 19:05:00.05   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Monitoring                      b4ly  Medium    Leaving Monitored Scope (Timer Job OfficeWebApplicationsCacheCreationJob). Execution Time=14.8229  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3892        02/18/2013 19:05:00.05   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Performance                     nask  Monitorable  An SPRequest object was not disposed before the end of this thread.  To avoid wasting system resources, dispose of this object or its parent (such as an SPSite or SPWeb) as soon as you are done using it.  This object will now be disposed.  Allocation Id: {ADFDB253-11D8-4E24-806E-DF9C81239608}  To determine where this object was allocated, set Microsoft.SharePoint.Administration.SPWebService.ContentService.CollectSPRequestAllocationCallStacks = true.  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

The problem is the "Office Web Apps Cache Creation" SP Job tries to run every 5 minutes but fails for some reason. The Cleanup job also fails that is scheduled to run daily.

Solution

I tried without luck:

• modifying the Cache DB's settings with Set-SPOfficeWebAppsCache
• moving the Cache Db onto a different Content Database
• removing the Word Viewing Service Application
• stopping Excel Calculation Services, PowerPoint Service, Word Viewing Service
• disabling and re-enabling the Office Web Apps Site Collection Feature

Then, I mounted the Office Web Apps ISO, and started a repair. Rebooted the server and good to go! No more errors.

UPDATE: Don't forget to run the SharePoint 2010 Products Configuration Wizard after the installation if your search doesn't work.

How to Restore SharePoint MySites from Backup

The Story

On Friday I had a very bad surprise. All managers from the company were waked with this emails like:

Subject: The My Site of X Y is scheduled for deletion

The My Site of X Y is scheduled for deletion in 14 days. As their manager you are now the temporary owner of their site. This temporary ownership gives you access to the site to copy any business-related information you might need. To access the site use this URL: https://mysites.portal.domain.local/personal/xy

Pretty uncomfortable. The day before this I deleted the AD Sync Connection from SharePoint's User Profile Service Application and this caused it. The sync job ran and with no connections SP thought that all the users have been deleted.

Theoretically there should be 14 days until these profiles are deleted but for some reason I can't see past activity for the use users.

Long story short, I need to restore MySites from a backup.


The Process

The restoring process is simpler than thought. Later I found out that I had to restore the User Profile Service Application as well, I will detail that later.

Restoring MySites from Backup

1. Unmount the MySites Content Database 

Unmount the MySites content database by going to Central Administration -> Application Management -> Manage Content Databases.

Select the MySites Web Application on the right

Check Remove Content Database and click OK

2. Restore the Database in SQL Server Management Studio from backup


3. Add back the content database in Central Administration

Central Administration -> Application Management -> Manage Content Databases.

Select the Mysites Web Application on the right

Click on Add Content Database and fill out the form

I had another issue: The profiles' data was wiped. I am referring to the content seen under the marked tabs:

This data is stored in the User Profile Service Application's databases (Profile Database and/or Social Database, not sure). So these had to be restored as well.

Restoring the User Profile Service Application from Backup

The process is fairly simple as long as you have the SQL backups.


1. Stop the User Profile Service and User Profile Synchronization Service in Central Administration -> Manage Services on Server

2. Restore the Profile DB and Social DB from backup on the SQL Server

3. Start the User Profile Service and User Profile Synchronization Service in Central Admin.

Unexpected: Failed to get legacy traces

SharePoint Server 2010 Enterprise.

Checking the SharePoint Logs, found the following errors repeating each minute:

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Monitoring                      nasq  Medium    Entering monitored scope (Timer Job job-diagnostics-io-intensive-query-provider)

OWSTIMER.EXE (0x17A8)                     0x3264  Extended Diagnostic Providers   SqlTrace Provider               2001  Unexpected  Failed to get legacy traces

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           6398  Critical  The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPIOIntensiveQueryDiagnosticProvider (ID c2f9c9c0-5d41-43a4-98bf-c59b007c3065) threw an exception. More information is included below.  You do not have permission to run 'SP_TRACE_CREATE'.

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           72ae  Unexpected  Exception stack trace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Bo...

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           72ae  Unexpected  ...olean returnStream, String method, DbAsyncResult result)     at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()     at Microsoft.SharePoint.Diagnostics.SqlTraceHelper.CreateTrace()     at Microsoft.SharePoint.Diagnostics.SqlTraceStreamer.Init(SqlTraceParameters parameters)     at Microsoft.SharePoint.Diagnostics.SPSqlTraceDiagnosticProvider.InitializeBackgroundThreads()     at Microsoft.SharePoint.Diagnostics.SPSqlTraceDiagnosticProvider.Sync()     at Microsoft.SharePoint.Diagnostics.SPDiagnosticsProvider.Execute(Guid targetInstanceId)     at Microsoft.SharePoint.Administration.SPTimerJobInvokeInternal.Invoke(SPJobDefinition jd, Guid targetInstanceId, Boolean isTi...

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           72ae  Unexpected  ...merService, Int32& result)

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Monitoring                      b4ly  Medium    Leaving Monitored Scope (Timer Job job-diagnostics-io-intensive-query-provider). Execution Time=9.3616 

Solution:

Grant the user running the SharePoint 2010 Timer (SPTimerV4) service, which is usually the Farm account, serveradmin or sysadmin rights on the SQL Server. Then, restart the Timer service.

Explanation:

blogs.msdn.com/b/kaevans/archive/2012/02/09/sharepoint-error-6398-you-do-not-have-permission-to-run-sp-trace-create.aspx - great article!

Some Advanced InfoPath Rules

In this tutorial I will create a "smart" InfoPath form that will change its contents based on some scenarios.

Scenario: 

> employees subscribe each month for some company benefit in the upcoming month.
> subscriptions are open from 24th till the end of the month otherwise closed.
> once an employee has subscribed it cannot subscribe again in that month (show instead a thank you page).

Solution:

This can be achieved by creating a Site Workflow that registers the data into a SharePoint List. The Workflow form is customized with InfoPath. In this post I will insist on the form.

Prerequisites:

1. Have a SharePoint List to store subscriptions. Fields:

• Person (Person type)
• MonthTag (String type. It will contain "2012-11" like strings to identify the month when the subscription was made. This will allow easy grouping and filtering for later processing.)
• SuperscriptionID (String type. Combination of Person Username and MonthTag to determine if a person already subscribed for that month. Its value is calculated by the workflow and filled in at creation. E.g.: "2012-11.Levente.Rog")
• Any other fields that need input from the user.

2. A Site Workflow that creates the subscription (adds an entry in the above mentioned list). End users will Start the workflow itself (by giving them a link). This Workflow will have the following steps:

• Generate the Month Tag (2012-11 like strings)
• Generate SuperscriptionID (2012-11.Levente.Rog -like strings)
• Create an Item in the Subscriptions List with the previously mentioned values. Using the Workflow Context:Initiator variable we know who is the logged in user so the end user does not have to fill out his own name in the form (that would be lame isn't?)
• Send a confirmation email

Upon request I can create a tutorial explaining in greater detail covering these workflow steps.

Form Customization

The form of the Workflow is the one that I will customize now.
This is how I managed to solve this scenario:


What I did is I created separate sections for three scenarios (disregard the 201-12. section for now, I will explain that later).

1. First scenario is when registrations are open. Two conditions have to fulfill for this section to show up:

• Date of month >= 24
• User has not subscribed yet in the upcoming month

To achieve this, I created the following InfoPath Formatting rules with the action to Hide the section.

• The expression: number(substring(xdDate:Today(), 9, 2)) < 24

• Any occurence of Subscription ID is equal to concat(substring(addDays(today(), 30), 1, 7), ".", translate(userName(), "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz"))
  
  
  What I did with that function is:
  Generate the "Month Tag" that is a unique string each month. Since we subscribe for benefits for the next month we calculate one month (addDays 30) forward and crop the first 7 characters from the ISO Date to get a 2012-11-like key. We concatenate that with the lowercase username. This way we'll get something like:
  2012-11.levente.rog . We also record that same string in the SharePoint list as well. This way we know if that user already subscribed for that month.
  - Any occurrence of Subscription ID - we get that by creating a second connection to the same SharePoint List (Data Ribbon -> To SharePoint List). If any of those empty comes out true, the section will be hidden.

2. If the user has already subscribed show a thank you message.
One rule I have here, basically the negated version of rule #2 seen previously:

• All occurences of Subscription ID are not equal to concat(substring(addDays(today(), 30), 1, 7), ".", translate(userName(), "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz"))

3. Show a registrations are closed message if date is <= 24th.

This is also a variation, negating the #1 rule used in the first scenario.

• The expression: number(substring(xdDate:Today(), 9, 2)) >= 24

4. That dummy "2012-12" section is just a calculated value containing the Subscription ID generation formula. For some reason, when I haven't actually used this formula, it was not evaluated in the rule. This section is always hidden from the end user (by creating a dummy rule: myfield does not equal "asdf" => Hide).

• Calculated Value XPath expression: concat(substring(xdDate:AddDays(xdDate:Today(), 30), 1, 7), ".", translate(xdUser:get-UserName(), "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz"))

That's it. Save and Publish.

The workflow is redirected to itself by changing the &Source= parameter in the URL the link to the workflow itself. For example:

http://sptest/Subscriptions/_layouts/IniWrkflIP.aspx?TemplateID={fd570692-3e3e-4c6d-a613-d11932bfc5e2}&Source=http://sptest/Subscriptions/_layouts/IniWrkflIP.aspx?TemplateID={fd570692-3e3e-4c6d-a613-d11932bfc5e2}

After completing the workflow, it will be redirected to itself again, showing the same form. But since the user now is subscribed, the "Thank you" section will show up this time.

SharePoint Workflow Cannot Access External List

SharePoint Server 2010.


I set up a Site Workflow that reads some properties of an employee from an external SQL Database.
> created a dbreader user in SQL Server
> added the SQL credentials to Secure Store
> created External Content Type in Designer
> created External List based on the previously created ECT
> user is able to see data in the External List
> created Workflow that reads data from the External List.
> running the Workflow will return empty data for any query from the External List
> following error in the logs

w3wp.exe (0x029C)                         0x3D4C  Secure Store Service            Secure Store                    efr5  High      ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app.

w3wp.exe (0x029C)                         0x3D4C  Secure Store Service            Secure Store                    7493  Critical  The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).

w3wp.exe (0x029C)                         0x3D4C  Secure Store Service            Secure Store                    efp6  Monitorable  GetRestrictedCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault). &nbsp

w3wp.exe (0x43B8)                         0x4320  SharePoint Foundation           Workflow Infrastructure         el2x  Medium    The workflow could not find the specified item in the external data source. Make sure the user has permissions to access the external data source and read items.

Solution:

The System Account had no permission over the Secure Store Object to read the credentials to the SQL Server. The System Account is the account which is running the Application Pool .

Central Administration -> Manage Service Applications -> Secure Store Service : Manage -> Edit the application ID -> Next -> Next -> Add the AppPool user to the Members list.

A really nice, detailed guide on Business Connectivity Services can be found here.

SharePoint Site Owner Cannot Modify Group Memberships

SharePoint Server 2010.
User is member of the Site Owners group, however he is not able to modify group membership. He can grant permissions only directly.

As seen in the screenshot, the New menu is missing when opening the Group's membership.

A possible problem is that the Group owner field in Group Settings is incorrect. Maybe a legacy setting from SharePoint 2007 or a manually created SharePoint group.

Solution: Add the Owners group as the group owner.

In this case, if the site's name is Dashboard, add Dashboard Owners as the owner of all 3 site-related groups: Dashboard Visitors, Dashboard Members, Dashboard Owners.

You may need Site Collection or Farm admin rights to restore these permissions.