THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Data Platform MVP from the Netherlands

Implement SSAS MD cell security using dimension security with blazing performance

You can read this blog post at this link:

This blog has moved to There will be no further posts on Please update your feeds accordingly.

You can follow me on twitter:

Published Monday, February 18, 2013 9:42 PM by jorg

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Anže said:


I have one secured measure and several calculated measures that use this secured measure. The thing is I would also like to secure all these calculated measures.

I implemented upper template and I still see the calculared measures? Is it possible to secure calculated measures.

BR, Anže

February 26, 2013 5:41 AM

jorg said:

Hi Anže,

Following the technique described in this blog post means you would create one _CHECKED calculation based on your measure and use this in all your other calculation definitions.

End users will still see the calculations, but when they use it in a pivot or report it will return NULL when they don't have the appropriate rights. So the calculations are secured.

Hiding calculations based on security is, unfortunately, not possible. Your measures on the other hand, will be hidden because you use dimension security for them.

- Jorg

February 26, 2013 5:50 AM

Laxmi said:

Hi Jorg,

I am working on cube where I need to implement dynamic cell security as not all measures need security but only few. I am kind of doing same as you have explained in your blog ..but I am not getting what I am trying to attempt\achieve. Below is my structure..


UserID UserAlias

1 Laxmi

2 Chris

UserSecurity ( this is bridge table which links to dGeo on areaid )

UserID AreaID

1 1

2 1

2 10

dGeo GeoKey AreaId AreaName

1 1 UnitedStates

2 10 Germany

I have factTable1 (GeoKey, A, B , C,D)

I need to have output where if I view the report I should see only measure A for United States as you see in usersecurity table my permissions.. and for A, C and D I should see data for all areas.. ( no security)

I didn't use dimension security as we need dynamic cell security based on who is login it should see the permission and allow that user to see data..

I create scope in calculations tab of cube CREATE MEMBER CURRENTCUBE.[Measures].[HideA] AS False , VISIBLE = 1 ;

SCOPE ([Measures].[HideA]); SCOPE (StrToMember("[d Users].[User Alias].& [" + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),"\")) + "]")); THIS = ([Measures].[User Security Count] = 0 ); END SCOPE; SCOPE ([d Geo].[Area Name].[All]); THIS = True; END SCOPE; END SCOPE;

And then added this code to celldata tab of role.. NOT ( Measures.CurrentMember IS Measures.[A] AND HideA )

When I browse the cube I shows me data for A and B for all areas instead of US..

Any idea\suggestion as to what am I missing ?

I have been struggling since more than a week to get this.. but no success :(

Thanks, Laxmi

August 14, 2013 8:32 PM

Prasad said:

hi laxmi,

i do have same problem now,do you find solution for same.



August 29, 2015 12:21 AM

Leave a Comment


About jorg

Jorg Klein, Microsoft Data Platform MVP from the Netherlands.
Privacy Statement