How to to restrict user from seeing table data in PowerPivot for SharePoint?

Question

Hi, 

I have published a PowerPivot report in SharePoint PowerPivot gallery which allow user to open the reports either in browser or in Excel (using the option "Open in Excel"). If user opens the report in Excel, he can see the actual table data (datasource) by clicking on 'PowerPivot WIndow' in Excel 2010. How can restrict user from seeing the table data? Can I put any restriction in SharePoint PowerPivot gallery? Please advise me.

Naush

 

 

Answer 1

Hi naush83,

 

Once the workbook is downloaded to a user's local machine there is no mechanism that prevents him or her to click on the PowerPivot Windows in Excel 2010. Once the user clicks on that botton, they will be able to see the data that is already stored in the workbook, but not in the data source. If the user who is opening the workbook localy in the Excel 2010 with PowerPivot for Excel has granted access to the workbook's data source, he or she will be able to refresh that data (which retrieves data from the data source).

One  thing you may want to consider, should you want the users not to see the data, is to create new workbook whose datasource is the workbook you already have. A linked workbook. This new workbook won't have an embedded database. You can wath our own Lee Graber how to do it here:

http://technet.microsoft.com/en-us/sqlserver/Video/ff944605

If you want to prevent certain users from download the workbook from the PowerPivot Gallery in the first place (so they cannot download it and open it in Excel client in their desktops), you can do so through SharePoint.

This TechNet article might help you with that:

 http://technet.microsoft.com/en-us/library/ff452082.aspx

 

Let me know if it worked for you or not...

 

Best,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 2

The lack of an ability to lock a user from seeing the table(s) used by powerpivot is an extreme drawback and will likely prevent me from using the program for most of my needs.  My data can be reported at an aggregate level but by law I have to ensure that individual records cannot be accessed.  This is extremely frustrating as the dynamic abilities of powerpivot are very nice but if it cannot protect data it is useless.