How to Create a Workflow to Change Item Level Permissions

Yesterday in our ShareLabs session a question was asked on how to display and/or secure data in a list based on criteria other than the author.  I am going to use colors as the criteria for the purposes of this post.  So the scenario is that we want to build a list to track something like, say.... oh I don’t know…. how about bicycles.  We only want to display certain colors to certain groups for whatever reason.  There are multiple ways to accomplish this, one of them being that we create views that filter the colors that we want to display, then create a page and drop a web part for each filtered view, and then apply Audience Targeting on each web part.  At first blush this looks like it would accomplish what we want it to but what happens when a user goes to the list itself instead of our web part page?  They are going to be able to see all entries in the list regardless of color, because of the permissions that they have on that list.

In SharePoint 2010 there are item level permissions that can be applied on lists.  Essentially you can mouse over each item, click on the drop down arrow, and choose Manage Permissions.

On the permissions page you can choose the Stop Inheriting Permissions button on the ribbon and then customize the permissions on that item.

Now you have the desired results across the board without needing to setup web parts with Audience Targeting.  However, this approach has a couple of flaws.  First, this method can be very daunting to manage especially for large lists.  Think of breaking the permission inheritance on each and every item in the list and then customizing the permissions.  Also think about managing it going forward, what happens when you have to add a new user to permissions?  Since inheritance is broken on the item you will have to manually add that new user to each item that they should have access to.  You could and should use groups to assign permissions to but it can still be a very time consuming process to get your permissions just right.

There is an easier way to accomplish this task and it comes in the form of a SharePoint Designer workflow that uses impersonation steps to change the permissions on the items in your list.  I will walk you through creating such a workflow as well as discussing some of the potential trouble spots.  Before we dive into SharePoint Designer to build the workflow we have some decisions to make and some preliminary work to do.

1.       We will need a list for our data.  This will also work for Document Libraries but for this example I am creating a list called Bikes with the following columns; Serial Number, Color, Manufacturer, Bike Type, and Suspension.

2.       What criteria do we want to use to set up the permissions?  For this example we are going to use the color column.

3.       How many different sets of users are we going to have?  In my list I am using 3 colors; red, blue, and green but I am going to have more sets of users than colors.  Our sets are going to be; red, blue, green, red and blue, red and green, blue and green, and all.

4.       On my site I am going to create a group for each set, 7 groups total.  Bikes-Red, Bikes-Green, Bikes-Blue, Bikes-Red and Blue, Bikes-Red and Green, Bikes-Blue and Green, and Bikes-All will be the group names.  As a side note, if I am going to be in Designer anyways and if I need to make multiple groups, I prefer to make my groups in Designer as the navigation is easier with the buttons on the ribbon always being present where in the browser it takes a few extra clicks for each group that you create.  Both methods work so use whichever one you prefer.

This is what the permissions look like for all entries in my list before running the workflow:

Now that we have all of the prep work out of the way let’s build a workflow.  If you haven’t done so already, open your site in SharePoint Designer 2010. 

What’s that?  You don’t have SharePoint Designer 2010 installed on your computer?  OK, we’ll wait while you go to http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16573 to download and install SharePoint Designer 2010.

1.       Once your site is opened in SharePoint Designer (SPD), click on Workflows in the Site Objects pane on the left.  In the New section of the ribbon, click on List Workflow and choose the list that you want to create the workflow for, in this case Bikes.

2.       Give your workflow a Name and optionally a Description then click OK.

3.       You will now be in the workflow editor screen with a blinking orange cursor in the Step 1 window.  Changing permissions through a workflow requires that we use an impersonation step so the first thing that we need to do is click above the Step 1 window so that the blinking cursor is above Step 1 and then click on  Impersonation Step in the Insert section of the ribbon menu.

After you have an Impersonation Step window you can click on the Step 1 window and delete it.  If you are creating a more complex workflow that will use multiple steps then feel free to leave the Step 1 window there so that you can use it in the future.

4.       Now that we have an Impersonation Step, the first thing that we need to do is set a condition that compares the value of the color column.  Click on Condition and choose If current item field equals value from the drop down menu.

5.       Click on field and choose Color then click on value and choose Red.  You screen should look like this:

6.       Next click on the Action button on the ribbon, scroll down to the List Actions section and choose Replace List Item Permissions.

7.       Click on the these permissions link to launch the Replace List Item Permissions window.  Click Add.

8.       Click on the Choose… button and select the following groups; Bikes-All, Bikes-Red, Bikes-Red and Blue, and Bikes-Red and Green then click OK.

9.       Next select the permissions that you want to grant to these groups.  To keep things simple I am going to grant all groups Contribute permissions so I select the Contribute checkbox and then click OK. 

10.   If you want to mix things up and give all groups Contribute and the Bikes-All group Full Control then simply add another entry to the List Item Permissions window and choose Bikes-All and grant them Full Control permissions.  This is probably a good place to talk about the potential for locking yourself out of being able to access items in this list.  If you are a Site Collection Administrator you have less to fear as you will still have access to the list items.  However, if you are a Site Owner and not a member of any of the groups that we created earlier on, then guess what…. after this workflow runs you will no longer have access to items in this list.  Make sure that this does not happen to you by either adding the site’s Site Owner Group or making sure that you and/or other site owners are in a group that you are specifying to have Full Control permissions on the item.  Don’t say I didn’t warn you!  Once you have all of the permissions granted to the right groups or users, click OK.

11.   Click on the this list link, leave Current Item in the List field and click OK.

12.   In the Insert section of the ribbon click on Else-If Branch then click on Condition and choose If current item field equals value.

13.   Click on field and choose Color then click on value and choose Blue.  You screen should look like this:

14.   Repeat steps 6-11 from above to add the Replace List Item Permissions action to the workflow, only this time for the Blue set of users.

15.   Repeat steps 6-14 for the Green users set.  When you are done your Impersonation Step should look something like this:

16.   Click on the Check for Errors button on the ribbon to verify that there are not any logic errors in the workflow.  This check does not ensure that your workflow will work as perfectly as you have envisioned, only that there are no infinite loops, dead ends, etc… in your workflow logic.

17.   If there were no errors, click Save.

18.   On the bread crumb trail just below the tabs under the ribbon, click on the name of your workflow to exit the editor and to get to the workflow settings page.

19.   On the workflow settings page find the Start Options section and choose the Start workflow automatically when an item is created and the Start workflow automatically when an item is changed checkboxes.  This will ensure that if someone changes the color field then the proper permissions will be applied.

20.   Save the workflow again after choosing your start options.

21.   The last step in SPD is to publish the workflow.  This is where things can get a little tricky because we are using an Impersonation Step.  This workflow is going to run under the security context of the user who publishes the workflow.  In most cases this fine but let’s explore a couple of possible scenarios that could render this workflow useless.  Suppose that in the future you are assigned to another team and your permissions are removed from the site.  At that point the workflow will no longer work and all new items will revert back to inheriting the permissions from the list.  Ok who are we kidding, no goes into the site settings and clears up permissions right?  Chances are that John who used to be on your team a year ago but has since moved over to Accounting is still a member of your site.  True, that does happen all too often so let’s take another approach, let’s say that in 6 months you take a job at another company.  It is very likely that your account will, at the very least, be disabled before you make it to your car in the parking lot.  At that point your workflow is broken.  I know, you’re thinking if that’s the case then why do I care, I don’t work there anymore.  That’s not going to win you friends and glowing references in the future so let’s do this right.  It is always a good idea to publish workflows that contain Impersonation Steps as a service account.  Service accounts generally don’t go away and your administrator can setup a policy on the backend so that the permissions of said account are not ever removed from the site.  So either risk alienating your coworkers by publishing the workflow as yourself or contact your SharePoint Administrator and have them publish it using a service account.

Now that your workflow has been published to your brand new, not populated with items, list - you are finished.  As you add items to the list, the workflow will run and set your permissions as you configured the workflow to do it. 

Oh, but your list already has a few entries?

No biggie, just mouse over each item, click on the drop down list, choose Workflows, and select your workflow name and click Start.

Huh?  Oh you meant there are a few hundred items in the list?  Ah, well check out my earlier post on creating a workflow to run on an entire list, the poor man's way for the answer to that problem.

Ok, your workflow has ran and now the permissions for your items should look similar to this:

If you see additional entries on the permissions list that have a permission level of Limited Access you don’t need to worry about them per se.  The Limited Access permission level is a system permission that acts as a place holder.  It could indicate that those entities have access to something either lower in the hierarchy or at a peer level such as a content type.  Limited Access does not indicate that they have any access to this specific item.  However, if you want to be sure that they do not have permissions to this item, click the Check Permissions button on the ribbon, put the user name or group name into the field and click Check Now.

That about wraps things up, until next time….

Kicking Off a Workflow on an Entire List The Poor Man's Way

I have a list that contains several items in it and I have a really cool SharePoint Designer workflow that modifies the properties of the items (columns, permissions, etc..) when it runs.  However, in SharePoint Designer I only have the options to run the workflow automatically when an item is created or when an item changes.  Did I mention that when I said a few items, I really meant a few hundred items, if not more?  I really don’t want to spend the next 2 days manually kicking off the workflow on each item in my list!

One possible solution is to add a Yes/No column to your list, do not add it to the default view, create a datasheet view of your list with the new column visible, and modify your workflow to reset the value in your new column once the workflow has ran.  Once all of that is in place, go to your new datasheet view, set the value in the first row and copy it on down to the bottom.  Let’s take this step-by-step:

1.       Open your List or Document Library and click the List tab on the ribbon menu.  Click on Create Column.

2.       Give your column a name, something like Run Workflow and choose Yes/No (check box) as the type.  Set the Default value to No and uncheck the Add to default view check box, then click OK.

3.       On the ribbon, click on Create View, Click on Datasheet View, give your view a name like RunWorkflow, under Display make sure that the new column that you created (Run Workflow) and one other, generally I choose the Title field or any other field that is going to show a unique value for the items in the list is selected, then click OK.

4.       Open the site that contains the list in SharePoint Designer, click on Workflows and then the workflow that you want to run.

5.       On the workflow settings page make sure that Start workflow automatically when an item is changed is selected and then choose Edit Workflow.

6.       Click on the screen above the first step of your workflow.  Then in the Insert section of the ribbon click Step to add a new step to your workflow.  You can rename the step so that it does not appear to be out of sequence with the other steps in your workflow.

7.       Click on the Condition button on the ribbon and choose If current item field equals value.

8.       Click on the field link and choose the column that you created in step 2.  Then click on the value link and choose Yes.

9.       Click Action on the ribbon and choose Set Field in Current Item.

10.   Click on the field link and choose the column that you created in step 2 and then click the value link and choose No.  Your step should look like this:

11.   Save and Publish your workflow.

Open your list with the view that you created in step 3.  Go to the Yes/No box on the first item, click on it to set it to Yes and then drag the bottom right corner of the cell to the last row of your list.  This should set all of the check boxes to Yes, kicking off your workflow on each item and thus setting the field back to no (no checkmark).

Unexpected error occurred while communicating with Administration Service when trying to configure a managed property

Problem:
This is an issue that had me banging my head for far too long.  Whenever I went into my FAST Query SSA, clicked on FAST Search Administration, clicked on Managed properties, and then either Add a new managed property or Edit an existing one I was presented with an "Unexpected error occurred while communicating with Administration Service" message.  After searching the web I came across this great blog post by Gavin McKay which has a few things to check to make sure that your FAST for SharePoint server is setup correctly.  After running through the list and verifing that I was good there I was still getting the error.

Solution:
What it came down to was the path statement on my FAST server.  You need to make sure that the \FASTSearch\bin directory is the first entry on your path statement.  On my server it was the 7th entry in and as soon as I changed it to be the first and then rebooted, I was back in business.

The sandboxed code execution request was refused because the Sandboxed Code Host Service was too busy to handle the request

Today I struggled with this little gem of an error when trying to activate the crmlistcomponent.wsp sandbox solution.  The error that I got was:

After scouring the web and finding various blog posts I came accross this one which held the answer.  In my case the answer lay in option IV choice A:

IV.  A fourth known cause is that SharePoint is trying to check for certificate revocations at crl.microsoft.com. The following are workarounds:

A. There is a registry key that is used by the sandboxed solution infrastructure and sometimes gets the wrong value. To ensure that it is set to the correct value, take these steps on all servers that are running the sandboxed host service :

1.On the server, click Start | Administrative Tools | Services.

2.On the Services dialog, scroll to SharePoint 2010 User Code Host.

3.Note the full user name in the Log On As column. You will need this information later.

4.Open SharePoint Management Shell.

5.Enter the following at the command prompt, including all punctuation.

(Get-SPManagedAccount –Identity “username”).Sid.Value

Replace username with the name you obtained in step 3. E.g., CorpDomain\FarmAdmin

This will return the user’s SID (Security ID), which you will use in a later step.

6.    Open the registry editor and navigate to:

HKEY_USERS\SID you obtained earlier\SOFTWARE\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\SoftwarePublishing

7.     Be sure the State key value is set to 0x00023e00.

8.    Restart the sandboxed host service on all servers on which it is to run. It cannot hurt to do an iisreset as well.

If you find that this solution does not work for you, check out the blog entry on the SharePoint Developer Team Blog for other possible solutions.

Introducing the Road Bikes

My first 2 posts were on SharePoint so I decided to dedicate next couple of posts to cycling.  To begin, let's talk about the bikes themselves.  To date I have 6 bikes total, 2 road bikes, a 29er for the mountains, a commuter for work, a fixie for around town, and an old mountain bike that I use mostly as a loaner.  I don't have a favorite per se, I just choose whichever one is best suited for the ride that day.  I ended up with 2 road bikes this year because I decided to upgrade towards the end of the season.  I started out the year with a Felt Z90 that is a great bike for an entry level bike.  I put in a boat load of miles on it throughout the summer training for LOTOJA which I will do a blog on in the coming days.

2009 Felt Z90

The 2009 Felt Z90 was my first road bike.  I bought it originally to commute to work as my other bike at that point was a mountain bike which at the time was still in Montana and I was in Utah.  I haven't modified it much, I changed the saddle from the stock to a Serfas because it was much more comfortable, swapped out the pedals for Crank Brothers Candy and added a Cateye Strada Cadence computer.  Now I use this bike on my trainer in the winter and the 700x25 tires come in handy for those questionable weather rides such as the FrontRunner Centruy in early April and the Bike the Bear ride in August.  I will be posting blogs for those rides in the next day or two as well. 

2011 Specialized Tarmac Comp Double 105

I picked up this beauty on Labor Day this year.  I was in the Logan, UT area scouting the course for my leg of LOTOJA when I saw that there was a sale at the bike shop in town.  I stopped by to check things out and saw 2 bikes that looked to be my size.  Sitting side by side was this bike and a Trek Madone 4.x and they were both my size, 61cm and 62cm respectively.  I am 6'5" and finding 2 bikes in my size was indeed a treat as it is very rare for me to be able to walk into a shop and be able to test ride a bike on the spot.  I had been toying with the idea of upgrading to a carbon frame but not having ever ridden one I couldn't really justify the expense.  So here was my chance not to try just one carbon bike but two!  I walked over to the bikes and started looking them over.  I picked up the Specialized first and oh my, I couldn't get over how light it was!  The I lifted the Trek and the same thing.  I decided to try the Specialized first so I took it up to one of the sales clerks and asked if I could take it out for a spin.  I gave him my license and off I went.  As I headed out of the parking lot and onto the road awesome is the first word that comes to mind.  The heavens opened up and the angels started singing.  This was my Excalabur!  I raced down the road and my body seemed to become one with the bike.  I turned right and had a slight climb ahead.  I stood up and began to really crank to see what she had in her and I noticed that it immediately lunged forward, accellerating much more quickly than I had on my Felt.  The responsiveness was amazing!  I kept pedaling up the hill and before I knew it I was up and over.  I was in flip flops so I didn't do anything too crazy on the ride but I did manage to go out and back for about a mile or so.  Right then and there I knew that I would be upgrading to a carbon frame bike.  I was eager to get back to the shop to try out the Trek.  I went on the same route as before and the Trek performed great.  However the geometry of the bike just didn't fit me as well as the Specialized did.  The heavens may have opened but the angels didn't sing and I did not become one with the bike.  The Trek was a fine bike, it just wasn't the one for me.

I got back to the shop and the salesman asked me what I thought.  I told him that I preferred the Specialized but I wasn't sure if I was ready to buy.  At this point I had only seen the regular price and I knew that I would have a tough time getting that purchase OK'd by my wife.  He then mentioned that during the sale it was 45% off.  After doing some math in my head I figured that it still might be tough to get the OK but not impossible.  Besides, it's easier to ask for forgiveness than permission right?  (If my kids are reading this then disregard that last sentence, I'm not the forgiving type.)  Just kidding.... but I would be lying if I said that didn't enter into my mind.  I tell the clerk that I'll take it!  He takes me and the bike up to the register, hands the bike to one of the guys in the shop to give it the once over and rings me up.  He mentions that they are throwing in a free jersey with every purchase but the biggest that they have is a large and I wear an XL.  I take it anyways, I figure that it might fit my wife or my son will grow into it soon.  As I am signing the receipt I remembered that I had driven my wife's car that day and it didn't have a rack.  I know, who in their right mind drives a car without a bike rack?  Luckily the bike was light enough to manuever into the back seat once the front wheel was removed.  Now I had a 45 minute drive back home, long enough I thought, to formulate a plan for damage control for when my wife finds out that I bought another bike.  I was thinking that I was going to have to crash on some couches for a couple of nights until things cooled down.

As I arrived home I still hadn't come up with a good plan so I decided to just wing it.  I pulled into the garage and got the bike out of the car.  Then I headed inside carrying the bag with the free jersey and a couple of water bottles and there was my wife sitting at her computer.  She asked me what was in the bag and I pulled out the jersey and told her that I got a jersey for her.  She asked how much it cost and I said "the jersey was free...".  She immediately knew something was up and when I saw the look on her face I immediately went through the mental roledex to try and figure out sleeping arrangements for that night.

"I bought a new bike" I said.  So one thing that I hadn't mentioned is that I had bought my fixie just a few short weeks before this one so I am sure that she was not thrilled to hear that I bought another bike.  We had also just bought her a new road bike a couple of weeks before that so I am sure that she thought that we were done buying bikes for a while.

I told her how things went down, including the part about the heavens and angels.  She didn't have the same look on her face but she didn't have her happy face on either.  I held out my hand and said, "come take a look at it" and she took my hand and we headed out to the garage.

My son just got into biking this year too, he is 11 and told me that for his b-day he wanted a road bike.  That was a very proud day for me indeed, and sure enough he got a bike for his b-day.  Once he heard that Dad had a new bike he raced out to the garage ahead of us.  When my wife and I got out there he was lifting it up with a look of amazement.  "It's so light" he said.  I guided my wife ove to the bike and said, "try lifting it".  She too was amazed at the difference in weight.  It was at that moment that I knew that I was going to be sleeping in my own bed that night.  I could tell that she still wasn't overly thrilled but she was OK with it.  I think that this was also the moment that she realized that I have an addiciton to cycling and that the best way to deal with it is to accept it.

I have only put on a few hundred miles onto the Specialized but so far I am loving it.  As I mentioned before I will be posting more on some of the rides that I did this summer in the next few days.

This form-template is browser compatible, but it cannot be browser enabled on the selected site

The other day I ran into this problem when a customer called and told me that they could not publish a browse-compatible InfoPath form to their document library.  They sent me a screen shot of the following error:

I knew that the server was using SharePoint Server 2010 Enterprise edition so my next step was to verify that the Enterprise Features were Activated on the site collection.  To verify this I went to Site Actions / Site Settings / and under Site Collection Administration, click on the Site collection features link.

I verified that the SharePoint Server Enterprise Site Collection features are activated.

Next I clciked on Manage site features in the Site Actions section of Site Settings.  I verified that SharePoint Server Enterprise Site Features was activated as well.
Once that was confirmed I checked the form library settings that he was trying to use to see if he had turned off the ability to open the form in a browser.  To do this I went to the Form Library, clicked on the Library tab on the ribbon, then clicked on the Library Settings icon and then clicked on the Advanced Settings link.  In the Opening Documents in the Browser section verify that Open in the client application is NOT selected.

All of this was checking out but yet he still could not publish his form to the site.  On a whim I checked his permissions and he was indeed a Site Collection Administrator.  My next step was to create a new Form Library and try it again but he was still getting the same error.  This was the point where things just weren't making sense.  No one else was reporting that they were having problems publishing browser-enabled forms so I decided to create a new site collect, add this user as a site collection administrator and have him try to publish his form to the new site.  Viola!  It worked.  However, I was expecting that result, this step was more of a sanity check.

On the new working site I clicked on the All Site Content link to get a list of all of the libraries, lists, and sites that were in the site collection.  One of the libraries that I noticed was called Form Templates with a description of "This library contains administrator-approved form templates that were activated to this site collection.".  This looked promising so I checked the original site to see if this library existed there as well.  Turns out that the library did indeed exist on the original site.  However, I noticed that there was not a description for the library on the original site.  I moused over the link for the library on the original site and looked in the status bar to see that the URL was http://site/Form%20Templates.  I checked the URL for the library on the new site and it was http://site/FormServerTemplates.  Now we are getting somewhere!  On the original site I opened up the Form Templates library and saw that there were a couple of Excel documents in there, I wasn't expecting that.  I opened the Form Templates library on the new site and there were no documents listed.  I noticed that the New Document control in the ribbon was disabled as well, where on the original site it was available.  It looks as though someone deleted the original Form Templates library and tried to recreate it afterwards.  I noticed that the Excel documents in there had a Modified date that was about 9 months back.  Being the optomist I checked the Recycle Bin but the library was nowhere to be found.

I decided that I would try to create a new Form Templates library with the settings from the new working site.  Once I got the Form Templates library back to the original settings everything worked perfectly.

Here are the steps that I took to restore the Form Templates library:

1. I moved the Excel documents to another document library on the site.
2. Deleted the existing Form Templates library.
3. Click on Site Actions / More Options.
4. Choose Form Library from the list of templates.
5. Click the More Options button.
6. In the Name: field type in FormServerTemplates.  (When you create a list in SharePoint, the title is used to generate the URL for the list.  We will change the title a little later in the process.)
7. (Optional) In the Description field type: This library contains administrator-approved form templates that were activated to this site collection.
8. Under Navigation choose No.  this will prevent the list from displaying on the Quick Launch bar.
9. Leave the rest of the settings at default and click on the Create button.
10. Click on Library Settings in the ribbon.
11. Click on Title, description and navigation.
12. Change the Title to Form Templates and click Save.
13. Next click on Advanced.
14. Clear the Template URL: field in the Document Template section and then click OK.

Corrupt Out Of The Box (OOTB) Workflows After SharePoint 2007 to 2010 Upgrade

Recently we performed a database attach upgrade of our MOSS 2007 farm to SharePoint 2010.  One of our Site Collection Admins reported that when he went into his document library settings to try and create an out of the box workflow, there were no templates listed.  This was happening on all document libraries in the site collection. I checked the site collection features and all workflow related features were activated.  I opened the site in SharePoint Designer and Workflows had nothing listed.  My next step was to see if deactivating/reactivating the workflow features would work.  I deactivated the SharePoint 2007 Workflows feature and reactivated it, then I went into the document library settings and there was still no workflow templates listed.  Next I tried deactivating the Workflows feature.  The deactivation went off without a hitch but when I tried to Activate it I got the following error:

After reviewing the ULS logs I found that there was an error creating a list called '_catalogs/wfpub' on the root site.  In SharePoint Designer I went to All Files/_catalogs and noticed that there was a sub folder called wfpub and within that folder there were empty subfolders for each of the OOTB workflow templates.  The icon for wfpub was a directory icon, not a list icon.  I renamed the wfpub folder to wfpub1 and reactivated the Workflows feature.  This time it worked, a new wfpub list was created and the templates were showing up in the document library workflow settings.

One piece of fall out from this is that by deactivating the Workflows feature on the site collection, it removed an OOTB workflow that was setup for a list on a subsite.  It was easy enough to recreate but you should probably go through all webs in the site collection and document the workflows that are there.