SharePoint Audiences are not Security!

In a nutshell… The SharePoint Audience feature is not security… ever. Audiences are used to filter (hide), not secure.

Some may consider the use of Audiences as “security by obscurity”, but it is not security.

From my SharePoint Security book…  (on Amazon – 2013/2016 version coming soon.)


SharePoint audiences are used to target content to specific groups of people by hiding it from those who don’t need to see it. These groups can be SharePoint groups, Active Directory security groups, Active Directory distribution lists and SharePoint global audiences that are based on user profile data.

Audience targeting can be used with:

  • List and library items, but only when displayed using a Content Query Web Part (part of the Publishing feature).
  • Entire web parts.
  • Top Link and Quick Launch navigation links. (when Publishing features are enabled)

Note: The Audiences feature is only available with SharePoint Server Standard and Enterprise editions. SharePoint Foundation has no support for Audiences. (Audiences is part of User Profile Services.)

Audiences are not security!

Audiences are usually described as being used to "target" content to a selected group. Audiences could also be described as being used to hide content from all users except for the target audiences. This second form sounds like security, but absolutely is not. While a list item or document web part might have a target audience, and non-audience members won't see the web part, if it is not otherwise secured they can still get to the item by using a direct URL or find it from search.

The Audience feature should be thought of as a filtering option, not security.

To filter list items using an Audience

List and library items can be filtered using the Audience feature using the Publishing feature’s Content Query Web Part. While the regular list web parts have an Audience feature, that feature hides the entire web part, not selected items. The Content Query Web Part is added to a site collection when you enable the Publishing features.

Four steps are required to filter list content using an Audience:

· Create a publishing site or enable the Publishing Infrastructure feature on a site collection.

· Enable the Audience feature on the list.

· “Tag” list items by Audience.

· Display the list using the Content Query Web Part.

Step 1: Enable the SharePoint Server Publishing Infrastructure Site Collection feature

First make sure there are no other reasons to not enable the Publishing features. (Policy, support, governance, etc.)

1. Go Settings (gear), Site Settings of the top level site of the site collection.

2. In the Site Collection Administration section click Site collection features.

3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

Step 2: Enable the Audience feature on the list or library

1. Go the list or library, click the LIST or LIBRARY tab in the ribbon.

2. Click List Settings or Library Settings.

3. Click Audience targeting settings.

4. Checkmark Enable audience targeting.
A new field named Target Audiences will now be displayed in the New and Edit pages for the list.

5. Click OK.

Step 3: “Tag” list items by Audience

1. Edit the properties of a list or library item.

2. In the Audience area click the Browse button ( ) and select an Audience.

3. Save the changes to the item.

Step 4: Display the list using the Content Query Web Part

1. Move to your page where you want to display the web part.

2. Edit the page.

3. Click the Insert ribbon tab and the Web Part button.

4. Click on the Content Rollup category and then click the Content Query web part. (If the web part is not listed then you do not have the SharePoint Server Publishing Infrastructure site collection Feature enabled.)

5. Click Add to add the web part to the page.

6. Click the web part’s dropdown menu and click Edit Web Part.

7. Expand the Query section.

8. Select the Source (the scope) for the rollup from one of the following: Show items from all sites in this site collection, Show items from the following site and all subsites, or Show items from the following list.

9. Select the List Type and the Content Type to select the content to display from the Source selected above.

10. In the Audience Targeting section checkmark Apply audience filtering.

11. Optionally add filters or Presentation options.

12. Click OK to save your web part changes.

13. Save the page and test the results.

Note: the Target Audience option in the Advanced section of the web part’s property panel is used to control if the entire web part will be displayed for an audience.

Search Web Parts vs the CQWP

Microsoft currently recommends using the Search Web Parts in many of the places that we might have used the CQWP. While search uses cached content, and can be quite a bit faster, the data is only as current as the last search crawl. (I.e. Completed tasks may still display as incomplete.) The CQWP is always using live data and can be Audience filtered.

Resources for the CQWP:

Display data from multiple lists with the Content Query Web Part:
https://support.office.com/en-US/article/Display-a-dynamic-view-of-content-on-a-page-by-adding-the-Content-Query-Web-Part-3e35bd58-d159-43d6-bfc7-77878b4a856d (or just do a web search for “Display a dynamic view of content on a page by adding the Content Query Web Part”)

To show web parts for an Audience

You can use Audiences to hide an entire web part from all users except for the selected audiences. Simply edit the web part, expand the Advanced section and select an audience.

Note: The SharePoint Server Publishing Infrastructure Site Collection feature is not needed for web part Audience filtering.

To display a Quick Launch or Top Link Bar link for an Audience

Links in the Quick Launch and the Top Link Bar can be filtered by Audience when the Publishing Infrastructure feature has been activated. Once this feature has been activated the Quick Launch and Top Link Bar options are replaced with a single Site Settings option named Navigation. In the Navigation page Quick Launch is called Current Navigation and the Top Link Bar is called Global Navigation.

Enable the SharePoint Server Publishing Infrastructure Site Collection feature:

1. Go Settings (gear), Site Settings of the top level site of the site collection.

2. In the Site Collection Administration section click Site collection features.

3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

To filter navigation links:

1. Go Settings (gear), Site Settings.

2. In the Look and Feel section click Navigation.

3. Scroll down to the Navigation Editing and Sorting section of the page.

4. Add or edit a Heading or a Link.

5. In the Audience area click the Browse button ( ) and select an Audience.

6. Set the Title, URL and other options as desired and click OK.

7. Test! The new navigation item should only be displayed for the selected Audiences.


Office 365 / SharePoint Online – Where’s Site Settings This Week?

SharePoint Online is kind of like the weather in Cincinnati… if you don’t like it, hang around, it will change.

Where’s Site Settings?

It depends… today, 10/23/2017 it’s here:

  • Classic UI pages: It’s where it has always been… in the Settings (gear) menu.
  • Modern UI pages: It’s…  Settings (gear), Site Info, Site Information, and then at the bottom of the popup panel, click “View all site settings”.
  • Modern UI Site Contents page: Top right corner, click Settings

I wonder will it will be next week?

Purpose of the change?

No real clue… If it was to surface the most often used Site Settings, they are not the ones I use most often. Are the most often used options on your list Delete Site, rename the site or change the description or icon.



A SharePoint REST API Tester with an AJAX and Workflow Writer


A JavaScript project to use with a Content Editor Web Part to test SharePoint REST API calls, and create AJAX sample code and SharePoint Designer 2013 Workflow steps. It includes over 40 ready to test samples to query SharePoint and to create and delete items, folders, lists and sites.

While learning the SharePoint REST API, I created a little REST tester Content Editor Web Part. Later when I explored SharePoint 2013 Workflow REST calls I expanded the tool to include step by step instructions to add the calls to a workflow. After presenting this at the Cincinnati SharePoint User Group and at the Nashville SharePoint Saturday I decided to take the time to clean it up a bit and share it here.

What you will need:

You can also download the file from the GitHub project.

This is the main screen.


This is partial list of the sample REST calls. A more complete list is at the end of this article, and I’ll be adding more over time.



The in the page test of a REST call.



The generated AJAX Code Sample



The SharePoint 2013 Workflow Steps for the Web Service Call



Steps to install to your SharePoint Site
  1. If your master page is not already loading jQuery, download jQuery (just about any version) and upload to the Site Pages library. 
  2. Download the SharePointRESTtester.html file to your PC. 
  3. Edit the file and update the line that loads jQuery to point your jQuery file or CDN.
  4. If your master page already loads jQuery, then delete the <script> block that loads the jQuery file.(the first line of the file)
  5. Upload the SharePointRESTtester.html file to your Site Pages library. (Copy the URL to the file.)
  6. Add a Web Part Page to your project:
    1. In the Site Pages library, click the FILES ribbon, click New Document and click Web Part Page.
    2. Enter a page name like "SharePointRESTtester". 
    3. From the library dropdown select Site Pages
    4. Click Create.
  7. Click Add a Web Part
  8. Add a Content Editor Web Part.
  9. Click the web part's dropdown and click Edit Web Part.
  10. Enter or paste the path to the SharePointRESTtester.html file.
  11. Click OK and then in the ribbon click Stop Editing.
  12. You should now see the tester. Click the dropdown and you should see data in the boxes. If not, then the jQuery library did not get loaded.
  13. Add to your Quick Launch or your Follow list!


To use the tester…
  1. Select a sample from the dropdown, or enter your own URL, Method, Header JSON and if needed, the Body JSON.
  2. Find the Do It! button. The first check box will actually run the code. *** Warning Will Robinson, stuff could get added, changed or deleted! ***
  3. The second and third checkboxes simple hide or show the JavaScript Ajax code and the SharePoint 2013 workflow steps.


SharePoint REST Examples for Queries

  • Get information about the current site collection.
  • Get information about the current web.
  • Get the Regional Settings for the current web.
  • Get the Time Zone for the current web.
  • Get SharePoint's list of Time Zones.
  • Get a list of all webs below the current web.
  • Get the primary site collection administrator (Owner).
  • Get the primary site collection Secondary Contact.
  • Get a web's LastItemModifiedDate
  • Get a list of lists from the current web. (all data)
  • Get a list of lists from the current web. (Just the title)
  • Get a count of items in a library.
  • Get a count of items in a library. (Option #2)
  • Get all items in a list/library.
  • Get all items in a library with filename and path.
  • Get a list folder's properties.
  • Get a count of items in a list folder.
  • Get all items in a list/library filtered by date.
  • Get all items in web level recycle bin.
  • Get selected properties of all items in web level recycle bin.
  • Get all items in a list/library filtered by a range of dates.
  • Search
  • People Search

SharePoint REST Examples for Lists

  • Create a new list
  • Add a new item to a list
  • Add a new folder to a list
  • Delete an item from a list using ID
  • Delete an item, to the Recycle Bin, from a list using ID
  • Update an item using ID
  • Delete a list
  • Delete a list to the Recycle Bin

SharePoint REST Examples for Sites

  • Create a new subsite.
  • Delete a site (Warning Will Robinson! Does not go to the Recycle Bin!)

SharePoint REST Examples for User Profiles

  • Get User Profile info about the current user.
  • Get all User Profile properties for a user.
  • Get User Profile info about a user's manager.

SharePoint REST Examples for Permissions

  • Get a list of Role Definitions for a site.
  • Get a list of Site Users. The ID is useful when setting permissions.
  • Get a list of Site Groups. The ID is useful when setting permissions.
  • Get a list of Site Groups by name.
  • Get a list of Site Groups where name contains 'string'.
  • Break inheritance on a subsite.
  • Break inheritance on a list.
  • Break inheritance on a list item.
  • Grant permissions (Role Assignment) on a list.
  • Remove permissions (Role Assignment) on a list.

SharePoint REST Examples for Filter Select and OrderBy

  • Get a list of Site Users who are not Site Collection admins. Get selected fields and sort.

SharePoint REST Examples for SharePoint 2010 style REST - _vti_bin/ListData.svc

  • Get a list of lists and libraries (EntitySets).
  • Find list items greater than a date.
  • Find list items between two dates.




SharePoint Date Search Tips


Applies to SharePoint 2013 and later.


A few SharePoint Search Tips!


Time Zone

Search internally stores dates in Universal Time. Because of this, a file uploaded at “2/7/2017 10:50 PM EST” will not be found with a search for “Write=2/7/2017” (or “LastUpdateDate=2/7/2017”). That file will be found with a search using “Write=2/8/2017”.


Date Ranges

You can create searches on date ranges using “..”.

      Example: write=2/1/2017..2/8/2017


Named Date Ranges

You can also use the names of some date ranges. Quotes are required around any range name that includes a space.

     Example: write="this week"


The supported ranges are:

  • today
  • yesterday
  • this week
  • this month
  • last month
  • this year
  • last year

But sadly… no “"last week”!


Comparison Operators

Note: All of the following operators work with the DateTime, Integer, Decimal and Double data types.





Less than


Greater than


Less than or equal to


Greater than or equal to


Not equal to






Creating Random Numbers in SharePoint Calculated Columns


One of my examples for tonight’s Cincinnati SharePoint User Group meeting! See you there!


I wanted to add a "motivational" message to a list of new sales. To be "fair" (i.e. I did not want to think and create a good algorithm!) I wanted the messages to be random. Something like this:


But… Calculated Columns do not support the Excel RAND() or RANDBETWEEN() functions.


So, how to get a random number???

Calculated columns do support the =Now() function. This returns a numeric value that represents the current date and time. If formatted as a Date, or Date and Time, then you will see the current date. But, if you format it as Single Line of Text you will see something like: 42,691.3977137731, or a few seconds later: 42,691.3983521875. The last number starts to look like a random number! And if accurate, it changes every .0000000001 of a day, or about every 0.00000864 seconds. Close enough for me.


Get a random number between 0 and 9.

This one looks easy, just pull off the last digit from NOW()!

    =RIGHT( NOW() ,1)

But.. there’s one flaw with this… The last digit of a fractional value is never zero!  (I.e. you will never see .111111110 unless custom formatted.)

So we need to pull off the next to last digit!

  =LEFT( RIGHT( NOW() ,2) ,1 )




Get a random number between 1 and 5

With just a little math we can limit the range a bit. As we don’t want the zero value we can skip the LEFT function for this one.

   =ROUND( RIGHT( NOW()) / 2+0.5 ,0)


Here’s a sample:



Get a random number between 0 and 999.

If you need bigger numbers, just return more digits:


As RIGHT creates a string (text), you will get leading zeros (“012”). To remove the leading zeros just do some math!

    = 0 + RIGHT(NOW(),3)


But… (there’s always a “but”), this will never return a value that ends with a zero. So… back to the LEFT function:

    =LEFT( RIGHT(NOW(),4), 3)

I.e. get the left three of the right four digits…



Random Messages?

This little exercise started out to create random messages. All we need to do is combine a random number with the CHOOSE function. As CHOOSE starts with item 1 and not item 0, we will need to add one to the random number.

   =CHOOSE( LEFT( RIGHT( NOW() ,2), 1) + 1, "Good Job", "Wow!", "Good Work", "Thanks!", "Could be better",
                      "Gold star for you!", "a free coffee for you!",":-)", "You are the MAX!","Do it again!" )




  • These are not guaranteed to be mathematically pure random numbers!
  • The values depend on the exact instant that an item is added to a list and will change with each edit. (But will not change with each view.)



Using PATCH with PowerShell’s Invoke-RestMethod


A story about a bug, an inconsistency and a solution…


I recently did a demo of using PowerShell's Invoke-RestMethod to create, read, update and delete (CRUD) data to a REST service written using ASP.NET's WEBAPI project template and ODATA controllers. Everything worked pretty much as expected except for using the PATCH method to change an existing item.

My GET worked as expected:

  Invoke-RestMethod 'http://localhost:41613/odata/Courses' | select -ExpandProperty value



My DELETE worked as expected:

  Invoke-RestMethod 'http://localhost:41613/odata/Courses(33)' -Method DELETE


My POST (create) worked as expected:

  $bodynew = @{ CourseCode='aa111'; Description='test'; Category='test'; Title='Test Course'}
  invoke-restmethod 'http://localhost:41613/odata/Courses' -Method POST -Body $bodynew



My PATCH (and MERGE) failed!

  $bodyupdate = @{ Title='Updated Title!'}
  invoke-restmethod 'http://localhost:41613/odata/Courses(34)' -Method POST -Body $bodyupdate


At least that gave me two hints… "no body" and "The inferred media type 'application/octet-stream' is not supported for this resource." The second one was probably the easiest to fix… tell it that I'm sending JSON.

   $headerJSON = @{ "content-type" = "application/json;odata=verbose"}


This got past the Invoke-RestMethod error when using PowerShell 4, but got a new error when using PowerShell 3! "Invoke-RestMethod : The 'content-type' header must be modified using the appropriate property or method." (it's a bug!) But now my ASP.NET WebApi application threw an error, "NullReferenceException". No data was received! (Remember the "no data" error?) So, maybe it was not in the expected format. When using the jQuery AJAX method you serialize your object into JSON before sending. Maybe that would work here:

  $bodyupdateAsJSON = @{ Title='Updated Title!'} | ConvertTo-Json
  Invoke-RestMethod 'http://localhost:41613/odata/Courses(34)' -Method PATCH -Body $bodyupdateAsJSON
      -Headers $headerJSON




The Question then is…

Why can I pass a -Body for a create (POST) without any additional work, but when I pass a -Body with an update (PATCH or MERGE), I have to pass the data as JSON and add a header to state that I'm sending JSON?


Will POST work with those two changes?

Actually it will! Invoke-RestMethod will do a POST using default PowerShell objects for -Body as long has you don't add a -Header that specifies JSON as the format. Invoke-RestMethod will also do a POST using JSON data as long as you do supply the right -Header. It's probably a best practice to be consistent and explicitly use JSON for both.



Live and learn…




Tested with PowerShell 3.0 and 4.0. PowerShell 3.0 fails when trying to set a header for JSON with "Invoke-RestMethod : The 'content-type' header must be modified using the appropriate property or method." (It's a bug!)

The .NET application:

  • Visual Studio 2015 ASP.NET Web API project with ODATA controllers
  • .NET Framework 4.5.2
  • Entity Framework 6.0
  • SQL Server in Azure
  • Test project hosted in Azure.



Forest and Trees Problem: "A network-related or instance-specific error occurred while establishing a connection to SQL Server"


A "Can't see the tree for the forest" problem.

There's an old phrase, "Can't see the forest for the trees", that in reverse, "Can't see the tree for the forest", applies to a recent "demo fail" of mine.

During a break in a C# class I was delivering last week I typed up a little demo for accessing SQL Server, and got an error. A quick read of the error said that it couldn't find the server, and it hinted at a protocol error.  

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)



Seeing the big "Named Pipes" tree standing there… I Binged and Googled and found all kinds of stuff… that did not help.

While the answer to the problem was clearly stated in the message above, "Verify the instance name is correct", I didn't see it as I was looking at all of the other "trees" in that little code forest. The "tree" that I needed to deal with in this case was a C# beginner error of putting a backslash in a string. (I copy and pasted it without looking at it!) The back slash is an escape character to flag the next character as a special code. In this case "\v" is the code for a Vertical Tab. So, I had created a connection string looking for a server named "(localdb)VerticalTab11.0".


What made this little error a bit painful was that in this class I had mentioned escape characters in C#, and how to deal with them, at least four times! Oh well…

To solve the problem, escape the escape character ("(localdb)\\v11.0") or mark the entire string as a literal string with the At sign ("con.ConnectionString = @"Data Source=(localdb)\v11.0 …").


For a list of the C# escape characters see this MSDN article:



You Can Now Create "Modern" Pages in SharePoint Online


It looks like "modern pages" are now rolling out to the tenants with the preview options on. You can still create Wiki Pages and Web Part Pages in addition to the new "Site Page" type. Here's both the new and classic Site Pages library “New” menus.

image   image_thumb[11]

The new pages have the "warm and fuzzies". Click to enter a page name. Note the Publish button. New pages are left checked out until "published". Major versions are enabled on the Site Pages library.


While web parts are not listed yet, you can add some "widgets" to the new pages. (Hey! There's a Yammer thing there!)



So… stay tuned to see what will appear next!




Hide the Windows Explorer Button in SharePoint Libraries


Tested in SharePoint 2013, 2016 and SharePoint Online.


The Windows Explorer view of a SharePoint library has so many issues that I'm often asked to hide it. Turns out that this is pretty easy to do. Two solutions:

  • Create a SharePoint Feature and deploy it to the desired site collections.
  • Add CSS to your master page, or to selected view pages.


Create a SharePoint Feature and deploy it to the desired site collections

This is the best solution! And it's been documented elsewhere: https://blogs.msdn.microsoft.com/tejasr/2010/07/19/how-to-remove-open-with-windows-explorer-button-from-document-librarys-ribbon-menu/

I would only add one more step to this solution… make sure the WSP file does not include an unneeded DLL so the solution can be deployed to SharePoint Online. (No code allowed!) The one extra step: In the project's Properties panel click "Include Assembly in Package" and change it to False.

Once the Feature has been installed in the Site Collection, just visit each subsite and activate the feature. This will impact all libraries in the site.


Add CSS to your master page, or to selected view pages

Add one little piece of CSS to your master page, or open SharePoint Designer and edit the library's views to add a CSS block and the button will disappear. If added to the master page then this will impact every library in the site. If added to a view page, then it will impact only that view.

This CSS will not work with the SharePoint Online “New Library Experience”. But then the new “experience” does not currently include a link for Windows Explorer!

The CSS:

<style type="text/css">
  #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
    display: none;

Note: The backslashes have been added to the ID due to the non-standard naming convention that uses periods.

If you would like site owners to still be able to see the button then wrap additional CSS in a SharePoint:SecurityTrimmedControl. Note that this control can only be added directly to a page, typically using SharePoint Designer. It will not work if added to a Content Editor Web Part.

<style type="text/css">
  #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
    display: none;
<Sharepoint:SPSecurityTrimmedControl runat="server" PermissionsString="ManageWeb">
  <style type="text/css">
    #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
      display: inline;




Auto-populated Choice Columns in SharePoint!


This was tested in SharePoint 2013 and 2016.

Had a list with 100,000 items with a State column. I found that some of my users did not know their state abbreviations. (KE is Kentucky?) After cleaning up the "nonstandard" states, I decided to convert the column from Single Line of Text to Choice.


After editing the column and clicking "Choice" I scrolled down and found that the list of choices was already populated!


For this to work for lists with more than the List View Threshold number of items (5,000 by default) you will need to be:

  • a server administrator, or
  • an auditor (Configured in Web Application settings, and only for under 20,000 items.), or
  • working with the list during Happy Hour!  (Offically “Daily Time Window for Large Queries”.)



The State column was now available in my Metadata Navigation Settings options.



Too Easy!

In the future when importing large amounts of list data I’ll just make the columns that should be Choice as Single Line of Text and then after the import change them to Choice.


Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.