Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005
Problem
I have seen the tip Dynamically Control Data Filtering in SQL Server Reporting Services Reports which showed how to leverage a SQL Server database table to filter sales data based on the user running the report. I have a similar requirement to implement dynamic filtering except my data is in a SQL Server Analysis Services (SSAS) cube and the users query the data with a variety of different tools. How can I implement dynamic security in my case?
Solution
SSAS has a robust security model that provides for dynamic filtering via a role-based security model. You create roles and assign Windows users, Windows groups, or both to be members of a role, then specify various security settings for each role.