SharePoint Development & Administration + InfoPath

Managing SharePoint site permissions can become difficult when the Site Collection expands to have many sites with different permission settings for each. This is especially true if the permissions are not inherited from a parent site, list or library, or item level permissions are used on items in a list or library.

Some third party reporting tools provide an overview of the permission applied across a site collection, with the possibility to drill down to more detailed information about the configuration of groups and permissions applied to each site. This article explains how to use Excel to create a single report with  permissions of multiple sites in one site collection.


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

Once a connection is configured for each of the sites, the spreadsheet will list permissions from each of the sites as dynamic data sources / connections are renewed. You may need to configure a data connection to insert additional rows if there is not enough space in the current spreadsheet to fit the updated information. Configuring data connections on a periodic background refresh time frame, or to download the data when the spreadsheet loads will help to ensure that the report will remain up-refresh to implement to date.

The report can be useful tool for SharePoint Administration, as it allows for permissions from multiple sites to be listed on an Excel spreadsheet, making it easy to see how the environment is configured. In some cases, specific permissions are required to view permission levels on a site or list so the user that the report must have access permissions for each of the sites included in the report.

Procedure:

1. Note the urls of the Permissions page on any site you want to display data in the report
2. Using Excel, select the "world" option from the Data Connections tab.
3. Enter the URL of the page permissions for one of the locations in the "Address" field
4. Select the region of the page you would like to draw data from
5. Press "Import" to import data. You asked where the location data
6. In the data connection properties, select "Refresh data when opening the file" if you want the data to be updated whenever the spreadsheet report opens.
7. On the "External Data Range Properties" dialog (right click on the data, or select the"Properties"option on the Connections tab), select the option to insert new rows for data to make ensure that data from the different connections doesn’t overlap.

A further improvement could be to dynamically configure data connections from a list of sites found either directly from the SQL database, or by scraping the list of sites on the "site hierarchy" page on the site collection root. This can obviously lead to a very large number of connections needed to generate a report with permissions of all sites, so an interim step should be possible to include data from a small selection of the sites each time. Since the permissions page for each SharePoint site has the same layout, a macro or similar should be written to run the Web Query using the dynamic list of URLs, and by automatically providing the region the page that contains the information required.


More about SharePoint Permissions:

Managing content using permissions - Show or hide ASP / SharePoint controls or HTML using OOB functionality provided by the SPSecurityTrimmedControl class, which can be included in a .aspx Page or Master page using SharePoint Designer or Visual Studio. SharePoint: Show or Hide Content based on Permissions provides further examples of using SPSecurityTrimmedControl to restrict content to users based on their permission on the current site, list or item.

A list of Programmatic uses of the SPBasePermissions Enumeration, which is used by many components of the SharePoint Object Model. SPBasePermissions can also be used without requiring code with SPSecurityTrimmedControlas in the reference above.

Another  SharePoint Reporting tip:

SharePoint Designer allows you to run a range of different reports on a site and content in a SharePoint environment.  Using a Macro that is run in SharePoint Designer, it is possible to automate the process of running reports across multiple sites.  The article SharePoint Designer (2007) - Run reports for all sites programmatically explains how to do this, and includes the macro code to run the process.  All you need to do is supply you list of sites to run the reports on, select the type of report to run and the save location for the files generated for teh reports and off you go...