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