Securing Your Data in Analysis Services - Professional SQL Server Analysis Services 2005 with MDX - page 1000

select <location>
from employee, location, [security]
where employee.[login]= '<login>' and
employee.[employee id] = [security].employeeid and
location.locationid = [security].locationid
The words within <> are the parameters passed to the stored procedure.
There is a potential for SQL injection attacks with the following query. We re-
commend use of a parameterized SQL query, which will help you to prevent a
breach of security. The stored procedure retrieves the results from the query
and forms the output string, which needs to be of the following format.
{[Location].<location>.<location>.&[<resultvalue1>],
[Location].<location>.<location>.&[<resultvalue2>],
[Location].<location>.<location>.&[<resultvalue3>],...}
The unique name for a member is represented as [Dimen-
sion].<Hierarchy>.<Level>.&[MemberName]. For attribute hierarchies the Hi-
erarchy name and Level name will be the same. If the key column and named
column for an attribute hierarchy are the same, the member in an attribute
hierarchy can be referenced as [Dimen-
sion].[AttributeHierarchyName][AttributeHierarchyName].&[MemberName].
Follow the same approach to build a string that will represent the set of mem-
bers for the members of a hierarchy in the location dimension. The values
resultvalue1, resultvalue2, and so on are the results from the SQL query
which you need to iterate to form the output of the function. You need to add
appropriate error handling to your stored procedure. Once you have compiled
your stored procedure, add the stored procedure to the assembly collection of
the database with the appropriate impersonation mode and Permission Set.
This stored procedure will require an external access permission because it
needs to access an external resource (the relational database).
Create a new role and add all the employees' logins to the membership col-
lection. Then specify read access to the cube and dimensions. Assuming the
name of the assembly that contains the getAllowedSet function is Secur-
ityMemberSet, specify the following MDX expression for the Allowed member
set.