How to break permissions inheritance on large libraries/ lists in SharePoint

This post describes a long-standing issue with managing permissions for large libraries or lists in SharePoint Online and gives a workaround for how you can break permission inheritance.

Intro

If you have ever tried to migrate a large volume of data into SharePoint libraries or lists you will have likely encountered an issue with trying (and failing) to break permissions inheritance on lists/ libraries following the migration.

I was recently dealing with this limitation myself as part of a migration project. We had migrated terabytes of data from on-premise file servers into SharePoint Online libraries and as we were beginning to break permissions inheritance on the library saw this error:

The issue

Faced with the problem of needing to secure lots of data that, based on the error above didn’t seem possible – I decided to refresh my memory on just what the limits of lists/ libraries are in SharePoint Online:

A list can have up to 30 million items and a library can have up to 30 million files and folders. When a list, library, or folder contains more than 100,000 items, you can’t break permissions inheritance on the list, library, or folder. 

Microsoft docs – SharePoint limits

With the above being true, the error mentions the list view threshold – which is the way SharePoint throttles and limits resources that govern the amount of data and throughput that can be managed. The list view threshold is set to approximately 5000 items by Microsoft and cannot be changed.

Now the issue with not being able to change the list view threshold, although unique to SharePoint Online isn’t a new thing – in fact it seems to have been a problem for lots of people for a long time. So if we are unable to change the list view threshold, what options do we have?

Well as it turns out the list view threshold error appears to be a bit of a red herring. I had taken a look at Microsoft guidance for managing large lists and libraries – creating indices within the library, but this had no affect as the total number of items in my library exceeded 100,000.

An example of a library that had over 100,000 files/ folders in SharePoint Online.

Workaround

The answer for me came from an inspired post on the SharePoint Stack Exchange! I give full credit for the simple, yet brilliant way to get this to work to Kasper Bo Larsen, who suggested that you should just delete enough stuff to get under 100,000 > break permissions inheritance > then restore the deleted items.

This works a treat!

Now it’s not perfect, nor would I call this a fix but if you have already migrated your data then this certainly will work for you.

Things to note

#1 Remember to break inheritance before any migration tasks begin!

I know this is a super obvious one and hindsight is always 20/20, but I’ve learnt from this mistake and built it into any future migration runbooks to break permissions inheritance before starting any migration tasks. It will save you a lot of hassle I promise…

#2 Trying to break permissions inheritance via PowerShell will yield same results

I had the thought during my investigations of this that maybe trying to break permissions inheritance via PowerShell might supersede some of the restrictions deployed to us mere GUI administrators – this was not the case and the PowerShell route fails just the same.

#3 There are no service limits or boundaries for the SharePoint recycle bin

I tried to find out if there were any limits to how much data you can restore from the SharePoint recycle bin in one go – I couldn’t find anything. With that said, I was able to restore over 60,000 files/ folders in one sitting back to it’s original location after breaking permissions inheritance so I don’t believe that will be a blocker.


How to show the folder path of a file in library views

Introduction

This post looks at ways in which you can show the folder path of a file as a standalone column within a SharePoint document library view.

UPDATE: I’ve updated this post after some comments asking how to do this for modern SharePoint libraries. Click here for more.

Classic SharePoint

The scenario

A common request I get is:

How do I see what folders/ sub-folders my files are in at a glance

– all users everywhere

Out of the box, there aren’t any columns available that you could potentially leverage to display this information in a standard SharePoint 2010 library.

The solution

So, just by adding one value in SharePoint Designer, here’s how you do it:

  • Navigate to the library you wish to change, create a new view under Library Tools > Library > Create View
  • Choose the relevant format of your view, give your view a name and press OK
  • Open SharePoint Designer > Open the site > open the library you were just working in
  • In the Views pane > click to open the view you just created
In SharePoint Designer, clicking on the view name will open the view in edit mode
  • In the code editor window, scroll down until you see something like the following:
<ViewFields>
	<FieldRef Name="DocIcon"/>
	<FieldRef Name="LinkFilename"/>
	<FieldRef Name="Modified"/>
	<FieldRef Name="Editor"/>
</ViewFields>
  • Add the following field reference in between the opening <ViewFields> and closing </ViewFields>
  • Add the field reference in the display order you would like it to appear in the view
Add the field reference to the View Fields list
  • Press the Save icon to save your changes
  • Press the Preview button to see your view in action in the browser

Now you will notice there is a new column being displayed “Path”, that is showing us the full location of the file or folder in the libary. You’ll also notice that this path will display data when at the library root, or in any folders or sub-folders in the library.

Library root displaying a files path
File in sub-folder displaying relative location

Bonus

Taking this one step further, what if we wanted to show files of a certain type, then create a view that groups these files by their folder location? Guess what, that’s exactly what I did!

  • Navigate to your library > create a new view as before, this time base your new view off the one you just created
  • If you wish to only show files of a particular type, use the filter by settings (for example below is filtered to only shows Word documents)
  • Make sure “show all items without folders” is selected
  • Press OK
Filtering to only show word documents, also showing items without folders
  • Back in SharePoint Designer > Open up the view you just created
  • Scroll down until you see the opening <Query> tag and add the following beneath it:
<GroupBy Collapse="FALSE" GroupLimit="30">
	<FieldRef Name="FileDirRef"/>
</GroupBy>

Save and preview your view, it should now be grouping by the Path field:

I know this has proven really useful for my company, so hopefully this helps out someone else too 🙂


Modern SharePoint

The scenario

With Microsoft retiring SharePoint 2010 designer workflows, plus the movement away from SharePoint Designer in general, a few readers have asked for a solution that works with modern SharePoint.

When researching this I considered whether suggesting to use SharePoint Designer 2013, as the above solution would still work in SharePoint Online using SPD 2013. But, as Microsoft say themselves although SPD 2013 remains supported, it’s depreciated – so I decided to go in a different direction.

The below example walks you through how you can create a flow in Power Automate to update a file after it’s been created to have the folder path shown in the document library view:

The solution

For this solution you will need to have access to create Flows in Power Automate, as well as an existing Document Library created in SharePoint Online:

  • Navigate to the document library you wish to show the folder path for
  • Add a single line of text column to the document library > give it a name (I called mine FolderPath)
  • Under the ellipsis, press Automate > Power Automate > Create a flow
  • In Power Automate, either use an existing, relevant template or start from blank
  • The trigger action should be When a file is created (properties only)
  • Set the Site Address and Library Name where you want to add the folder path
  • Insert a new step > select Update file properties.
  • Set the following values for the update file properties step:
    • Site Address: same as previous step
    • Library Name: same as previous step
    • Id: ID
    • FolderPath: Folder path

NOTE: The FolderPath within the Update file properties step is the custom column we created earlier. The Folder path (highlighted in red) is dynamic content available within the step in the flow. The folder path dynamic content is the path to the folder the item is in, relative to the site address.

Ensure you select the system Folder path dynamic content to pull the right data into the custom FolderPath column.

Here’s the flow in it’s entirety:

At this point test and save your flow to make sure it is working as expected 🙂

Bonus #1 – turn your folder path column into a hyperlink column

So if like me you want to take this one step further, wouldn’t it be good if we could easily make our newly showing folder paths, actual hyperlinks to the folders? Well the good news is you can!

  • Navigate back to your document library > click on the FolderPath column > Format this column
  • Under Apply formatting to make sure FolderPath is selected
  • Paste the following JSON into the custom formatting box:
{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "a",
  "style": {
    "color": "blue",
    "font-weight": "bold"
  },
  "attributes": {
    "target": "_blank",
    "href": "='YOUR SHAREPOINT SITE URL' + @currentField"
  },
  "txtContent": "@currentField"
}

NOTE: for more information on turning field values into hyperlinks, check out this awesome sample from sp-dev-list-formatting.

  • Press Save
  • Your FolderPath column values should now be legitimate hyperlinks that click through to the relevant folders

Bonus #2 – update existing files in the document library

This was another suggestion from a reader with regards to how to update files that existed in the document library before the flow was created.

Running a flow manually for individual files

When I began to consider how to do this I started by looking at ways to manually start the flow.

It appears the only real way to do this is to create a new column that adds a button next to each file, that allows you to run the flow. I’m not really enamoured by this approach as it doesn’t seem ideal to have an extra column to run a flow showing on every file in your library. If this is something you would like to pursue then I would recommend this great article by WonderLaura who has the process of creating a button to trigger a flow covered!

Update our flow to update all files if folder path is empty

My solution to this problem was to update the flow we created earlier to get the properties for all files in the library, then add a condition that checks if the FolderPath column is empty, then if yes runs our flow as before.

  • First, I added a Get files (properties only) action which gets all the files from the source library
  • Then I added a new Condition action, which simply checks if the FolderPath column we created is equal to null. You will also notice a new Apply to each action will be created
  • I then moved the previous Apply to each action into the “If yes” condition
  • I left “If no” blank, as it is ok to leave a condition blank if you don’t want it to do anything

Here’s the updated flow in full, which changes highlighted in red:


Problems creating list or library views based on created date

The situation

Data retention and deletion…I’m sure this is a something that anyone involved in Office 365, SharePoint on information management in general gets fed up of saying since the recent GDPR legislation!

Recently we have been rationalising and cleaning up our data in preparation for moving to Office 365. We are starting with SharePoint as the first target repository or silo of content.

The general consensus is to delete files and folders over 7 years old unless there is a pre-existing data retention policy to adhere to. So the next task is to identify those files that fall within our threshold, and ultimately delete.

Luckily, we have Tree Size Pro and ShareGate so I was able to relatively easily identify the files in question (there were a lot!).

The setup

As our SharePoint environment is a) rather full; and b) rather old, I made the decision to incrementally delete files rather than en-masse to mitigate risk, targeting the lists/libraries containing the most out of date content. I started by creating a view in the first library – library A with the following parameters:

  • Standard library view
  • Filtered by Created Date if less than or equal to 01/01/2011
  • Folders or Flat: Show items inside folders
    Show this view: In all folders

(all other settings are left default)

Results this returned looked good, I could see folders and files in this view that matched the criteria – brilliant! Based on my previous statement I decided to delete in batches out of working hours, again to mitigate risk. I deleted first from library A, then from the first stage and finally from the second stage recycle bin all in this fashion.

The problem

I had permanently deleted around 50% of the total volume of content to be deleted from library A when we started to receive reports of current files being ‘missing’ from library A…not a good day.

After these reports were investigated they were indeed true. It turns out that when folders are included within a library view, folders that match the filter will be shown in the view, regardless of whether the files inside match.

We tested the view exluding folders and all the files returned matched the filter criteria. The same results were demonstrated from a SharGate report of the same nature. The report of all files over 7 years old brought back folders over 7 years old, but they also contained files that were newer.

Conclusion

At present, we are not entirely sure as to why these filters are not able to drill down past a top-level folder. It appears to be difficult to specify via view settings to only show files within folders, including the folder itself that matches the criteria.

We have decided to omitt folders from our reports and views going forward and to solely focus on files as this is the most reliable way we can delete files.

Bonus: for those of you with ShareGate, heres an example of my report we created to bring back all files over 7 years old, excluding folders. I ran this report across the entire intranet application over a weekend and it worked a treat 🙂

SG-report

Hide a SharePoint list or library from view all site contents

Have you ever been asked to hide a list or library from a SharePoint site? If so, you go straight for selecting ‘no’ to displaying the list or library on the Quick Launch or removing it from the navigation. However, your eagle eyed users notice the handy view all site contents option and see that it is still listed there – they want it gone!

Luckily, all you need is SharePoint Designer and it is as simple as a click of a button…

(These steps were created using SharePoint Server 2010)

  • Open the site that where list or library resides in SharePoint Designer
  • Under Lists and Libraries – Select the list or library you wish to hide
  • On the main list settings page – find the Settings section
    SPD
  • Check the Hide from browser option
    hidefrombrowser

Thats it! when you option the view all site content page now, that list or library will no longer be showing. Also, if you want to re-instate it at a later date, just un-check the box and it will re-appear.

This also works for SharePoint 2013, 2016 and SharePoint Online, under the site contents page.

Page declared a record or placed on hold and is read-only

If you’ve ever been in the situation where you try to edit a page in SharePoint and you see this seemingly unchangeable message appear at the top of the page stating:

“Page has been declared a record or placed on hold and is read-only”

Fear not! I’ve seen this message appear and it usually occurs when a user (or service account) is operating as a system account. This could have been set manually by said user if they have access to the web application via central admin:

If this is the case, you can overwrite the system account check in via SharePoint Designer. You’ll obviously need the correct permissions to access SPD beforehand! In the following example I’m using a SharePoint 2010 environment. To do this:

  1. Connect to your web application in SharePoint Designer
  2. Navigate to All Files – Pages
  3. Right-click on the page that is currently locked
  4. Undo checkout

NOTE: You won’t be able to view the individual pages within each Page Library if you navigate through Lists and Libraries. This space is used to view and manage the settings for each list type you have. All Files takes you to the ‘root’ of your web application, where you can see everything that sits under the web application.

The SharePoint date format problem

I know what you’re thinking, what date format problem? Well this is something that’s cropped up for me time and time again so let me explain. Most requests I get for new SharePoint lists will usually contain a variety of custom date columns and said list will also require that the default display form shows a unique view of this data.

On the default display form, you will notice that the custom date columns will be showing data that looks like this:

bad date format

Well this just won’t do, will it? The good news is we can fix it!

To be able to customise the display form, you will need to be able to connect to your SharePoint environment using SharePoint Designer, if you don’t have it you can download it from Microsoft here:

SharePoint Designer 2010 (32 bit)

For this example, we are only focusing on one custom date column, at the end of the post I will summarise how to fix this issue for multiple custom date columns.

  • Open SharePoint Designer and connect to your environment
  • Navigate to the lists and libraries and open the list you want to change
  • Within the Forms section – New Form
SharePoint New Form

(I’d recommend creating a new display form for no other reason than to avoid potentially breaking the active one).

  • Give the form a logical name so you can identify it at a glance as a display form (I usually go for DispForm2)
  • Press OK
  • The code behind the display form will open, don’t worry too much about this we are only interested in a small portion of the code.
  • In the Ribbon – under the Home Tab – Press Advanced Mode
Advanced button on ribbon
  • If it’s not already, at the bottom of the form – change the view to split view
SharePoint split view
  • Scroll through the design view of your form until you find the custom date field – select it
  • In the code view, make sure the following line is highlighted:
    <xsl:value-of select="@Start_x0020_Date"/>
    (NOTE: the bits after the @ will be the name of your date field)
  • If you amend this line of code to look like this:
    <xsl:value-of select="ddwrt:FormatDate(string(@Start_x0020_Date), 2057, 3)"/>
  • you should see something like this:
    11 June 2014
  • Save the display form
  • Press Preview in browser to double check the format date function is working as expected
  • Close preview browser, close the display form
  • In the forms section of the list – select your form
  • In the Ribbon – Set as Default
set as default

That’s all there is too it! It might seem like quite a few steps but it isn’t too bad once you get comfortable with the forms code view.

If you have more than one custom date column that you need to format, just amend the code of each custom date column using the example above as a guide (just make sure if you copy/paste the code that each @ name is different.

FormatDate function and locales explained

What our updated code is doing is inserting a FormatDate function, which allows us to add the locale parameters to the end of our line code (the 2057, 3). The locale parameters control what the output of the FormatDate function will be, here are some examples of the outputs and locale parameters:

OutputLocaleFormat
3/23/200910331
3/23/2009 12:00 AM10332
Monday, March 23 200910333
12:00 AM10334
Monday, March 23, 2009 12:00 AM10337
3/23/2009 12:00:00 AM103313
Monday, March 23, 2009 12:00:00 AM103315
23/03/200920571
3/23/2009 12:00 AM20572
23 March 200920573
00:0020574
23/03/2009 00:0020575
23 March 2009 00:0020577
00:00:00205712
23/03/2009 00:00:00205713
23 March 2009 00:00:00205715

If you are looking for a list of all the available locales you can find them here:

https://msdn.microsoft.com/en-us/library/ms912047(v=winembedded.10).aspx

That’s it for now, if you’ve got anything you’d like me to cover feel free to get in touch or leave a comment!

Hiding NEW! from items in lists or libraries

This is my first ever blog post…scary! I’ll start with a really easy solution to a problem I was tasked with solving by one of our departments…

Ever wanted to completely remove any reference to the pesky NEW! icon from freshly uploaded documents or list items? Yeah me either…well if in case you ever felt so inclined to do so here are some very simple steps to remove the icons from any new documents or items on a page.

This example is a SharePoint 2010 web application with a standard publishing site collection active:

    1. Navigate to the page you want to remove the NEW! icons from and begin editing
    2. Add a content editor webpart to the page; I added it to the bottom of the page but you can add it whenever suites
    3. Click on the down arrow to open the webpart menu – Select Edit Web Part
    4. You’ll now notice the content editor webpart has changed, it will now say ‘Click here to add new content’, click here!
    5. In the ribbon – Editing Tools menu – Format Text tab – Press HTML – Edit HTML Source
    6. In the HTML editor, copy and paste this little bit of CSS:
      <style>
      IMG.ms-newgif {display:none;}
      </style>
      
    7. Press OK on the HTML editor

    Voila! The NEW! icons have vanished and we can move on with our lives…the next little bit is totally optional, but I think for completeness it makes sense to do. We’re just going to rename and hide the content editor webpart so that it’s not visible and any editors know not to touch it:

    1. Click on the down arrow to open the webpart menu – Select Edit Web Part
    2. In the content editor webpart menu – expand Appearance
    3. Change the Title to ‘Do not delete’
    4. Change the Chrome Type to None
    5. Press Apply and OK

    That is all there is to it, we have successfully removed the NEW! icons so they are no longer visible and also hidden the webpart which contains the tiny snippet of CSS that makes the change.