Master-SharePoint.com

Managing site permission in SharePoint can become an onerous task when an environment expands to many sites with various permission setting applied for different users.  This is especially the case when permissions are not being inherited from a parent site, list or library, or when item level permissions are being used on items in a list or library.

Third party reporting tools do exists to allow a SharePoint administrator to view n overview of all permissions used across a site collection, with the ability to drill down to show more detailed information about the configuration of groups and permissions applied to each site.  This article explains how to use Excel to generate a spreadsheet report that lists the permissions of multiple sites in a site collection.

This is achieved using data connections and a "Web Query", by selecting the "From the Web" option in Excel 2007 to configure a data connection.  The address to the site permissions page for each site is added to a separate Web Query connection, which retrieves the specified page from SharePoint, then extracts data from the regions specified when configuring the data connection.  If the table that lists all users/groups and the assigned permission levels is selected as the region to use, the spreadsheet can the retrieve this data from SharePoint and display on the Excel sheet.

Once a connection has been configured for each of the sites that you with to include in the permissions report, the spreadsheet will list permissions from each of the sites dynamically when the data sources / connections are refreshed.  You may need to configure the data connection to allow additional rows to be inserted if there is not enough existing space in the spreadsheet to fit the updated data.  Configuring the data connections to perform a periodic background refresh, or to refresh the data when the spreadsheet loads will help ensure that the report stays up-to-date.

The report can be beneficial tool for SharePoint Administration, as it allows permissions from multiple sites to be listed on one Excel sheet, making it easy to see how the environment has been configured.  In some cases, specific permissions are required to be able to view permission levels on a site or list, so the user who runs the report must have access to view permissions for each of the sites included in the report.

Process:

1. List the addresses for the permissions page on each site you would like to have data displayed for on the report
2. Using Excel, select the "From the Web" option from the Data Connections tab.
3. Enter the URL of the permissions page for one of the sites into the "Address" field
4. Select the region of the page that you would like to pull data from
5. press "Import" to import the data.  You will be asked where to place the data
6. In the data connection properties: select "Refresh data when opening the file" if you would like the data to be updated each time the spreadsheet report is opened.
7. From the "External Data Range Properties" dialog (right click on the data, or select the "Properties" option from the Connections tab), select the option to insert entire rows for new data to ensure that the multiple connections don't overlap each other once updated.

A further enhancement may be to configure data connections dynamically from a list of sites either retrieved directly from the SQL database, or by scraping the list of sites on the "Site Hierarchy" page on the site collection root.  This could obviously result in a very large number of connection required to view all permissions, so an intermediate step would need to be incorporated to allow data from a single or small selection of sites to be retrieved and displayed at any one time.  As the permissions page of each SharePoint site has the same layout, a macro or similar should be able to be written to configure the Web Query using the dynamic list of site URLs, and by automatically specifying the region on the page that contains the data required.

More SharePoint Administration Tutorials.