WebmasterHub.net Blog

Using an Excel spreadsheet and a data connection to the site permissions page of a site in SharePoint, you can display the permissions from the site on a worksheet. By adding multiple data connections to the spreadsheet to the site permissions page on other site in the SharePoint farm, or another SharePoint farm, you can list the permissions for multiple sites on one worksheet, or in the same file on separate sheets.

A data connection that can be used to retrieve the permissions from a site in SharePoint is to 'scrape' the data directly from the site permissions page of the using the web interface proved by SharePoint and a “Web Query” data connection in Excel. Each data connection can be configured to refresh at intervals, when the spreadsheet loads, or to only allow manual refreshes. If there are many connections in one sheet, you should consider staggering the refresh for each so that they are not all executed at the same time. As a separate web request to the SharePoint site must be completed for each connection, the SharePoint server may experience periods of high load if all connections are refreshed at the same time. Configuring each connection to refresh at intervals so that no two are refreshed at the same time should balance the load on the server. Sheets with a small or moderate number of connections can be configured to refresh at the same time if required, but you should test the impact on performance when refreshing data connections if the spreadsheet is to be used frequently or by a large number of users.

SharePoint Site Permissions Report Using Excel