SharePoint Online Hub Site – Take Inventory Using PowerShell

I was playing around with SharePoint Online Hub Sites today and noticed something interesting…

You might be asking yourself what are Hub Sites?! Read all about them here: 

Note: As of 5/7/2018 Hub Sites are at 100% WW roll out to Office 365 per 

In my Office 365/SharePoint Online tenant I created 2 hub sites and associated a few brand new team sites to each hub. (Read more about creating hub sites here:

Navigation/branding was propagating to all sites in each hub, but I was curious on how to take an inventory of all these sites (which sites are associated to which hub). I dug open the SharePoint Online Management Shell and did a Get-Member against Get-SPOSite. There are 2 new values – IsHubSite and HubSiteId


(As of 5/8/2018) I cannot run a simple Get-SPOSite | Select Url, IsHubSite, HubSiteId (This may work down the road, but does not work at this time). It displays false for IsHubSite for each site and displays all zeros for each HubSiteId

Connecting to each SPOSite individually works through (You can also run Get-SPOHubSite to see only the Hub Sites in the tenant):

I came up with the following script to loop through all SharePoint Online Site Collections and display the URL, Whether or not it is a hub site, and what the hub site ID is. This could be expanded upon, but is something to get you started and get accurate counts of how many hub sites are in the tenant and how many/which child sites are associated with each hub:

Screenshot for proof:

Hope this helps!


SharePoint 2013/SharePoint 2016 – Applying Cumulative Update removes all users with db_owner

Hey All – Recently ran into an issue where a customer had AvePoint installed in the farm (Which requires the DocAve account to have db_owner) and after every CU it’d remove an user that was manually granted db_owner. This is actually a security measure in place to make sure there isn’t an errant account left with db_owner permissions on the database. There are times when we want that account to stay (For things like third party tools or RBS..though they should find a way to use SP_DATA_ACCESS instead!) There is a registry key BypassDboDropMember that was added to SP2013 in the October 2016 CU and to SP2016 in the October 2017 CU (Note: You will need to be on these CU levels to gain access to this functionality) for bypassing this behavior:

Instructions For SP2013 (From link above):

Note: For SP2016 just change the registry subkey to 16.0

  1. After you install this update, you can follow these steps to control the metafiles optimization:
    Start Registry Editor:

    • In Windows Server 2012, if you’re using a mouse, move it to the upper-right corner, go to Search, enter regedit in the search text box, and then select regedit.exe in the search results.
    • In Windows Server 2008, go to Start, enter regedit in the Search programs and files text box, and then select regedit.exe in the search results.
  2. Locate and then select the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\15.0\WSS\
  3. On the Edit menu, point to New, and then select DWORD Value.
  4. Enter BypassDboDropMember, and then press the Enter key.
  5. In the Details pane, press and hold (or right-click) BypassDboDropMember, and then select Modify.
    In the Value data box, enter 1, and then select OK.
    Note If you don’t want to bypass the behavior, you can set the value to 0.
  6. Exit Registry Editor.

Add-SPShellAdmin and SPWebApplication.GrantAccessToProcessIdentity. What’s the difference?

TLDR – The Add-SPShellAdmin and SPWebApplication.GrantAccessToProcessIdentity are very similar in what they do, but there are a few key differences:

  1. Add-SPShellAdmin: Should be used for granting admin accounts access to run PowerShell commands against the farm. This grants the account 2 database roles (SharePoint_Shell_Access and SPDataAccess) to the specified content database.
  2. SPWebApplication.GrantAccessToProcessIdentity: Should be used for granting service accounts access to the content database. This grants Full Control User Policy to the Web Application and adds the account to the SPDataAccess role for the specified content database.

OK, if you’re still reading…here’s the longer version:

      • SharePoint_Shell_Access Role:
        • Members of the SharePoint_SHELL_ACCESS role have the execute permission for all stored procedures for the database. In addition, members of this role have the read and write permissions on all of the database tables.
      • SPDataAccess Role:
        • The SPDataAccess role will have the following permissions (SPDataAccess should be used for all object model level access to databases):
        • Note: The SP_DATA_ACCESS role replaces the db_owner role in SharePoint 2013. (From
          • Grant EXECUTE or SELECT on all SharePoint stored procedures and functions
          • Grant SELECT on all SharePoint tables
          • Grant EXECUTE on User-defined type where schema is dbo
          • Grant INSERT on AllUserDataJunctions table
          • Grant UPDATE on Sites view
          • Grant UPDATE on UserData view
          • Grant UPDATE on AllUserData table
          • Grant INSERT and DELETE on NameValuePair tables
          • Grant create table permission
  • SPWebApplication.GrantAccessToProcessIdentity (
    • Used for service accounts requiring elevated access to the content database(s)
    • Most Common Use – Needs to be set for service accounts (If using least privileged) running Excel, PerformancePoint, SSRS, etc.
    • First, this sets a full control User Policy for the Web Application:
    • clip_image004
    • Then, this adds the user to the SPDataAccess role for the specified database(s) for the Web Application:
    • clip_image005


Long Live SSRS SharePoint Integrated Mode

On 11/17/16 the SQL Server Reporting Services Product Team announced that starting with SQL Server v.Next, there’ll be only one installation mode for Reporting Services: “Native” mode. Check out the blog here –

I’ve blogged about SSRS a little bit here and there so making sure everyone is aware of this as you look into future deployments of SSRS!


Enumerating SharePoint Sites for Office 365 Groups With PowerShell

On 11/8/16 Microsoft made true of announcements from August 2016 and announced that Office 365 Groups with connected SharePoint Online team were now available. Link –

The SharePoint admin inside me began to think about managing all of these new site collections/cluttering up my SharePoint Admin Center! The good news (Spoiler Alert)…It doesn’t! One of my friends (Brian Kinsella – had the same question, so I decided to dig in to figure out how to get at all of these new site collections.

Most of you with an active Office 365/SharePoint Online deployment are probably familiar with the SharePoint Admin center:


I went ahead and created a new Office 365 Group called “TestO365Groups”

To do this I did the following:

  1. Go into the Mail App > (Under Groups) Clicked the + button
  2. Fill out the appropriate information (Name, Description, Privacy, etc.)
  3. This provisioned a site collection at following URL (I checked this by clicking the Files tab within the group) –

Now onto the concerns above!

I was not able to see this site collection in the SharePoint Admin page and I was not able access Office 365 Groups via SharePoint Online PowerShell cmdlets (Get-SPOSite)


Exchange PowerShell to the rescue! Here’s what you can do to find all those Office 365 group URL’s:

  1. Fire up Exchange Online PowerShell –
  2. Open PowerShell as Admin and connect to the O365 tenant using the following PowerShell:
Run the following command to get each Office 365 group and its associated SharePoint site URL:
Check it out (There’s a lot of properties you can work into this script if you’d like..109 to be exact):



SharePoint Patching 101 – Don’t forget to save all those CAB files in the same folder as the EXE!

Hey All – I’ve seen people run into this issue a few times now so I figured it’d be worth a quick post. A lot of the SharePoint 2013 Cumulative Updates include 3 files – 2 CAB’s and an EXE. These used to be included in the same self-extracting executable file, but now are 3 separate downloads. Make sure to select ALL 3 from the Microsoft Download Center and then make sure they are all in the same folder when you go to run the EXE and patch that beautiful SharePoint farm of yours.

These guys…



Major Life Changes

Major life changes recently! Here’s the summary:

  • I left Skyline in December 2015
  • I moved to Austin, TX in December 2015
  • I started at Microsoft in January 2016

It’s been a hectic needless to say I haven’t been able to blog much lately. First, lets discuss the new job. I’m now working at Microsoft as a SharePoint Premier Field Engineer. What is that you might ask? Here’s some good information written by others:

Here’s my personal take – There are 2 types of PFE’s: Transactional and Dedicated. Transactional is more project-based (Risk Assessments, Workshops, etc.) and usually travel quite a little bit more. I fall under the dedicated role, which means I am assigned certain customers and work with them on a daily basis. It’s been a mix of everything from learning the environments, best practices for configuring different services/features, demos, chalk talks, etc. It’s definitely a change of pace from consulting as PFE’s do not touch the keyboard! I think it’s been very beneficial to take a step back from pumping out installs, upgrades, health checks, etc. and focus on how the product actually works/what business value it can provide.

OK, let’s talk about the new digs. I’m loving Austin, TX so far! There is so much character in this city and the attention to detail at a lot of the venues is amazing. There is a ton of events/concerts (Live Music Capital of the World) plus the food scene is amazing. Also, there is a lot to do here in the outdoors/sports realm – I’ve been doing a lot of CrossFit, rock climbing, hiking, kayaking, biking, etc. As far as the move itself I feel like I could really write an entire blog series about this, but it doesn’t really fit in with this whole SharePoint Admin vibe. Moving required a lot of organization, patience, and diligence. Here’s a few tips/lessons learned:

  • DO YOUR RESEARCH! I found a great Reddit page to get started –
  • Use an apartment locator. They don’t cost anything (The apartment/condo they refer you to has a marketing budget for apt locators). I was able to find a nice apartment sight unseen (The apt locator uploaded some videos on YouTube for me to watch). I highly recommend Gregg at Austin Cool –
  • I personally decided to sell a ton of stuff before moving. I realize this may not be in the cards for everyone, but it was kind of a freeing experience. Getting quotes from moving companies really opened my eyes on the cost. For Example – It would have cost me ~$250 extra to move my futon..which I want to say only cost about that much brand new. I sold the futon to a friend and had a couch shipped to the new apartment (Free Shipping).
  • I decided to ship my car and stuff…both of them ended up arriving toward the later part of the delivery window (The moving companies will give you a few day buffer). I recommend you keep enough clothes/essentials on you so you don’t end up wearing the same shirt three days in a row.

There’s a ton more I could share on this, but I figured I’d at least give a high level overview. Now that I’m a little more settled into the new city and new job I should be able to blog on a more consistent basis. One more shameless plug – I’ve been helping out with the Austin Office 365 & SharePoint Users Group. I highly suggest you check the MeetUp page and if you’re in/visiting Austin check out a meeting!

SharePoint SSRS – Orphaned SQL Agent Jobs Causing Subscriptions to Fail

Weird/crazy issue recently. Subscriptions were getting bogged down/failing in a SSRS SharePoint Integrated Mode environment. This issue seemed sporadic..some days subscriptions would fire off at the scheduled times and sometimes they’d get stuck processing for a few hours before users would receive the subscriptions. After some troubleshooting/digging into this issue we noticed something off. We ran a query in SQL to compare the SSRS Subscriptions with the SQL Agent Jobs on the server. These numbers did not match..There were around 70 additional SQL Agent Jobs on the server..and they were not attached to a subscription. Opening one of them up shows that it was a SQL Agent Job created by SSRS for a subscription..but no subscription was associated. Disabling these jobs fixed the issues.

Here’s the SQL script (This renames all SQL Agent Jobs to have a prefix of ZZZZ_ and disables the job):

SharePoint 2013 InfoPath Form: Object doesn’t support this property or method ‘addeventlistener’ in IE 11

During an upgrade project we noticed that one of the pages that displays an InfoPath Form was throwing the error:

Object doesn’t support property or method ‘addEventLister’

InfoPath Error

This error wasn’t appearing anywhere else, so it was isolated to this specific page and also it worked in Chrome (The user was on IE 11). This led me to believe it was an IE 11 issue. I found the following post:

Adding the site to compatibility mode for IE 11 users fixed the issue..


SharePoint 2010/2013 Content Query Web Part..Please Open in Office Web Apps..Ugh, I loathe you right now

First off, thank you very much Ben Prins for getting me moving in the right direction on this one –

Here’s the scenario: A client was looking into rolling out Office Web Apps as the default open behavior for documents in a recently upgraded SharePoint 2013 farm (Started as a 2007 farm and upgraded to 2010 and 2013 throughout the years). Cool right? Follow this document ( and turn off OpenInClient and you should be rocking and rolling in the deep with those web apps..

Everything was looking great except throughout the site they were using content query web parts #CQWPFail. Content query web parts have their place and this client did not want to revamp a ton of pages and replace them with the shiny new SharePoint 2013 search web parts (The CSWP can span site collections like a boss, but the CQWP is pretty simple/easy to configure if you’re just looking at one site collection..unless XSLT is involved. Keep reading..). The content query web parts had no honor..they refused to acknowledge the OpenInClient setting. Not cool CQWP…

Since this was an upgraded SharePoint site as a troubleshooting step we create a brand new “Vanilla” SharePoint 2013 site collection and did a quick test. These CQWP’s seemed to have a little more honor..If the query was set to a specific list/library it would open in the web app. If the query was set to a site collection/site would try to open in client. Unfortunately that was the entire reason the client wanted to use CQWP’s back when they set it up in cross sites and surface documents using custom content types.

I think you know where this is going…time to brush up on those XSLT skills. After some research I found this page which states the files used for the CQWP: I did a (insert favorite file comparison tool here..I used WinMerge) against these 2 files (Comparing the 2010 upgraded site to the vanilla 2013 site):

  • /Style Library/XSL Style Sheets/ContentQueryMain.xsl
  • /Style Library/XSL Style Sheets/ItemStyle.xsl

What do you know??…there were differences. We updated the 2010 upgraded site’s ContentQueryMain.xsl and ItemStyle.xsl files and now at least queries directly to lists/libraries started working.

After this I found Ben’s awesome blog post and ran through the steps on there (I did have to make a few changes so I’ll post my detailed steps and I posted comments on his blog):

  1. Crack open that ItemStyle.xsl file (I checked it out first and then opened with NotePad)
  2. Right underneath this line (Since we’re editing the default style…you could create you’re own, but we wanted to update all existing web parts without too many changes)

<xsl:template name=”Default” match=”*” mode=”itemstyle”>

Paste the following lines:

  • Some things to note about this:
    • ?web=1 is what forces the document to open in Office Web Apps. Pretty nifty..instead of using a hard-coded link to WopiFrame.aspx and trying to parse the LinkUrl field..which I tried and failed because the URL passed to WopiFrame.aspx must contain be in this format: http://webappurl/sites/sitecollectionurl/siteurl/_layouts/WopiFrame.aspx?sourcedoc=/relative path to file
    • Feel free to add additional entries for doc/xsl/ppt
  1. After this I found the <div class=”link-item”> and updated it with this code:

Check these guys out:

2010 Fails on ALL Queries:


2013 is a little better..but falls short when a query is set at the “site level”


Here’s the site collection with the updated XSLT (Check out that sexy hyperlink at the bottom!)


Cool Stuff. Also another plug for Ben Prin’s blog…check out this post:

The XSLT snippet from this post allows you to see all fields and values that are available..which was super useful in troubleshooting.