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
http://www.mosha.com/msolap/ppt/131%20Dynamic%20Security.ppt
Dynamic Dimension Security in Analysis Services 2000 (presentation notes, project, info, ppt etc) - by Dave Wickert
http://www.mosha.com/msolap/samples/Dynamic%20security.zip
Customizing dimension security - by Russ Whitney
http://www.windowsitpro.com/SQLServer/Article/ArticleID/27305
Use UDFs in the definitions of dimension security
http://support.microsoft.com/?id=816480