Forum Discussion
ways to restrict access to full data set
assume i have a data set that contain data for >>100 offices. The data being confidential, is there a way to setup such that the report will only show the office data that the login user is responsible for? e.g. a NY logged on user will only see/filter NY data; a LON logged on user will only see/filter LON data
3 Replies
Definately! Though you can only do it by using a SQL Server Analysis Services (SSAS) Tabular model. You would want to implement row based security based on your given regions - which you can do with individual Roles, or by using more dynamic DAX filtering in your Role settings. Essentially, Power BI sends your SSAS instance the username of the current user, and you can leverage that username in DAX based filters to limit data returned.
Linked is a great tutorial using the Adventure Works database from Microsoft.Jorg Klein has written an easy to understand blog-post on this: http://sqlblog.com/blogs/jorg_klein/archive/2015/03/19/power-bi-analysis-services-connector-security.aspx
His conclusion however (March 2015), was that the sharing functionality within Power BI did not comply with what sounds logical when using effective usernames. He shared a dashboard with another user, but the original effective username was sent to SSAS... not the effective username of the user the dashboard was shared with.
Perhaps this has changed over time, or there is a reasonable explanation for this.
I'm not sure if the topic-starter wants to do an SSAS-implementation, so also wondering if we could use the effective username in a normal SQL DB setup. E.g. create views (to apply Row Level Security) and use the effective username to pick the right data...
- koenverbeeck
Advocate II
It's now possible using the new Row Level Security feature and the USERNAME() function.
An explanation:
