Dimension Security Stored Procedures in SQL Server Analysis Services SSAS 2005

Problem
I have seen the tip Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005 which showed how to define roles in a cube to limit the members of a dimension that are available to the members (Windows users and/or groups) of the role.  In that tip the dimension members were specified by simply selecting them via clicking a checkbox.  In my case I have a security implementation in the data source to my cube where there are a set of constantly changing rules that determine who can see what.  What I need is to be able to leverage the existing security implementation in the data source from the cube.  How can I do that?

Solution
The role-based security model in SSAS provides three ways to specify dimension security; i.e. what members of a dimension are available to members of a particular role.  The tip that you mentioned demonstrated the basic capability where you can specify an allowed set or a denied set of dimension members.  You do this by simply selecting or unselecting the dimension members.  The other two options allow you to define the allowed or denied set via an MDX expression or call out to a stored procedure.  I'll cover the MDX expression option in a future tip; in this one we'll look at how to leverage a stored procedure to dynamically generate the allowed set of dimension members for a role.