SharePoint 2013 BI, Tabular instance , Security, Roles, EffectiveUserName, PowerView (Part 2)

Now that your tabular instance and the associated PowerView report are up and running (see my first post), let’s focus on the security issue :

The context is that the same cube must be used by several customers and each customer can only see his own data (kind of multitenancy).

To acheive this you have to play with roles.

In SQL Server management Studio let’s go to the (non SharePoint) Tabular instance

We are going to create a new Role called “IsaRole”

Right click on Roles, name the Role IsaRole and check Read.


In Membership, add the (end) User account associated with this role; for instance, I want the user Contoso\ivc to be associated with this role.


Now I want to specify the filter associated with the role : let’s move to Row Filter and add the following DAX expression on T_Items :


This means that members of the IsaRole will only see the items associated with the beverages catagory.

Let’s log in as contoso\ivc and let’s click on the PowerView report we created in the previous post : we only see the beverage category


Let’s modify the report by selecting only the fields Name field and Qtity Consumed and we will see this:


Again, the chart is automatically filtered on beverages –> that works !

How does that works since we didn’t set up Kerberos ? How come that the user identity has been transmitted to the Analysis Service ?

Let’s add a trace to our tabular instance by using the SQL Server Profiler :



Let’s refresh our report (here I’m logged as contoso\ivc in another console).

In the profiler, you will see this textData : isaRole


And when you click on isaRole, you will notice that a set of XML elements are provided to the Analysis Service, like the EffectiveUserName pointing to CONTOSO\ivc :


So again you don’t need Kerberos (that I recommend anyway) to achieve this kind of double hop .

The next question is : can I achieve the same result with Excel Services ? Because until now PowerView cannot be used on most mobile platforms; Is that secure ? well…it can be dangereous; we will see that in the next Post.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s