If you were given an opportunity to create a “cubed” solution for 50,000 different users, would you create one cube with 50,000 roles? Would you create 50,000 cubes – one for each customer? What would you do?
This is what Mosha Pasumansky and Dave Wickert from the product team suggest for SQL Server 2000 - of course it all changes in SQL Server 2005. The problem in SQL2000, if you use the native method of using roles to implement security, with a large number of user roles, the cubes just grow and grow - which is not good. But there is another way and thats:
Dynamic Dimension Security in Analysis Services 2000 presentation - by Dave Wickert
Dynamic Dimension Security in Analysis Services 2000 (presentation notes, project, info, ppt etc) - by Dave Wickert
Customizing dimension security - by Russ Whitney
Use UDFs in the definitions of dimension security