背景：
一个分析数据库，包含多个产品，全国各个地区的业务数据（比如销售数据等），
要求
1：各个产品负责人查看自己产品的数据
2：各个地区只能查看自己的数据
方案：
SQL 2005 有基于角色的授权服务，通过设置产品和地区角色，来控制对数据的访问。
步骤：
0：在域上设置帐号和安全组
建立安全组：产品（mbiproducts）,地区（mbidepartment）
如果查看产品、地区数据的账号尚未建立，则建立相应帐号
1：在地区维度和产品维度添加帐号属性（处理数据时应将账户的数据导入该属性）
2：添加角色【产品】，
账号映射：
数据源为：read
关键点之一【Cell Data】：[产品].[账号].currentmember=StrToTuple("[产品].[账号].&["+LCase(UserName)+"]"),这是控制该角色不允许访问其他没有授权的Cell（访问时显示N/A）.
关键点之二： StrToTuple("[产品].[账号].&["+LCase(UserName)+"]")，
这用过控制使用该维度的时候只显示已授权的维度,其他的维度不会显示（因为显示出来数据的N/A）
注意启用
，这样Total是已授权的Total而不是真的Total。
到此，功能设置完毕。
测试Tip：
使用多维数据集(Cube)的浏览器（Browser）来测试基于角色的权限最方便。
只要简单点击左上角的切换用户(Change User)即可任意切换到所需用户，
最终效果：
管理员用户，可看全部品种：
单个产品用户，只看单个品种：
基于地区的授权和上述基于产品的授权过程完全相同。
你的图片脏了，擦干净吧 回复 引用 查看
通过角色控制现在好像是唯一的方法,我后来比较倾向于加透视,然后建立角色和透视的关系.
不过最头疼的还是走IIS代理,我现在想到的方法是有多少个角色就建立多少个IIS下的端口,就是那个dll,然后让每个接口模拟不同的身份,好麻烦的说.楼主关于这个有没有更好的方法呢? 回复 引用 查看
没看太明白 回复 引用 查看
想请教一下，父子层次的维度的权限怎么处理呢？ 回复 引用 查看
@wangdh
能说具体点吗 回复 引用 查看
比如有个机构维度，他的结构是父子层次的。
不同的用户可以查看不同的机构数据。
这些用户对应于AD账号。
目的是做到可以灵活的定义每个AD账号的数据权限。
像这种情况我考虑不清楚应该怎么建这个维度或者说数据库表结构 回复 引用 查看
另外我想问一下，你这种权限控制是否是每个地区只对应一个AD账号或者角色？
每个地区的数据权限是否允许被2个不同的账号或者角色得到？
最近比较忙，刚看到你的回复，请见谅 回复 引用 查看
授权的关键点在于构造一个表达式，比如上面使用的：
[产品].[账号].currentmember=StrToTuple("[产品].[账号].&["+LCase(UserName)+"]")
你可以根据你的结构构造相应的表达式。
只要你可以构造表达式，这个结构是可以任意设计的。 回复 引用 查看
@wangdh
父子结构应该也是可以的。
这里好像有个例子：
http://blogs.technet.com/paolt/archive/2005/02/17/375497.aspx
Parent child dimensions in Analysis Services 2005
Recently I worked with a customer in the sales force reporting area. The problem is very common: there is a hierarchical sales organization, and every sales manager needs to see sales performance of his own sales network. Changing dimension of type 2 is a nice variation of the problem (in that each sales manager sees the performance of a salesman only for the period of time he reported to him). We worked with parent-child dimensions in Analysis Services, and we used Reporting Services to distribute customized reports to every salesman.
Parent child dimensions in Analysis Services are regular dimensions where an attribute exists with the type properties set to parent. That attribute drive an unbalanced hierarchy inside the dimension. The attribute with key type still exists to keys the dimension elements. Also attributes of regular type can exists normally.
The parent attribute has some interesting capabilities, beside the fact that it allows to browsing the dimension by sales organization structure, as expected. A member of the parent attribute and the corresponding member of the key attribute are not idempotent, but they share member properties. This means that the parent attribute contributes to define the cube space, where each member of the parent attribute (not surprisingly) exists with any member of the key attribute being one of its children (at any level). For example consider the following salesman table supporting the Salesman dimension:
Key Parent Name Region Alias
------------------------------------------------------------------
1 1 Bob CA domain\bob
2 1 Steve CA domain\steve
3 2 Charles CA domain\charles
4 4 Frank WA domain\frank
5 4 Miriam WA domain\miriam
Salesman.Parent.[Level 3].&1 (Bob as an individual in the parent child hierarchy) exists only with Salesman.Salesman.Salesman.&1 (Bob as an individual). Salesman.Parent.[Level 2].&1 (Bob as a parent in the parent child hierarchy) exists with Salesman.Salesman.Salesman.&1, Salesman.Salesman.Salesman.&2, Salesman.Salesman.Salesman.&3 (Bob, Steve and Charles as individuals).
What it is more interesting is that any member in the parent attribute has access to member properties in the key attribute. For example the following expression is valid:
Salesman.Parent.Currentmember.Properties("Region") = "Seattle"
I have found member properties extremely useful in a couple of cases.
First of all you can implement cell level security using a formula similar to the following:
IsAncestor (filter (Salesman.Parent.Members, Salesman.Parent.Currentmember.Properties("Alias")=USERNAME).Item(0), Salesman.Father.Currentmember).
That formula first filters the parent child dimension by the alias member property, getting the parent member corresponding to the current logged user account, then it returns the rooted tree (there are a couple of exceptions on this that need to be checked out in the expression, but the formula is generally valid). I didn't test it with dimension security, but it should work.
Member prop are also very useful with Reporting Services, when you want to include sales information about a sales manager into the report, along with some details. Trouble is that the sales manager has aggregated sales information associated with him, then if you drag attribute information into the report (i.e. Region) what you get are sales information by that attribute. Instead you want the aggregated sales information for that sales manager and the region where the sales manager has customers in. This is a typical reporting need, and the only way I have found to do that is accessing the sales region as a member prop from the sales manager.
Published Thursday, February 17, 2005 8:00 PM by paolt
回复 引用 查看
又如：http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm
或者使用google可以搜到更多例子吧
http://www.google.cn/search?q=sql+analysis+2005+UserName+currentmember&complete=1&hl=zh-CN&newwindow=1&rlz=1B3GGGL_zh-CNCN203CN203&start=10&sa=N 回复 引用 查看
我不太明白[产品].[账号].currentmember=StrToTuple("[产品].[账号].&["+LCase(UserName)+"]")，这样设计不是因为产品维度里有个账号属性吗？
是不是每个产品只能属于一个账号呢？ 回复 引用 查看
@wangdh
帐号属性是我们设计字段，所以能够对应到几个帐号依赖于我们设计的结构。
如果不是设计为属性，而是一个儿子级的维度那就可以有多个了 回复 引用 查看