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:

Dimension Security

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