Microsoft KB Archive/293815

= INF: Dimension Rules for Security Role Not Saved When Programmatically Set =

Article ID: 293815

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q293815



SUMMARY
When applying permission rules on an OLAP dimension for an OLAP role, the rules are not applied until the role is edited in either the Database Role Editor or the Cube Role Editor in the Analysis Manager, and the OK button is clicked.



MORE INFORMATION
This behavior occurs when you programmatically set role permission rules for dimensions and the permission expression used in the program is incomplete. The behavior may also occur when a private dimension is improperly referenced while defining rules for a role at the database level.

When role permission rules for dimensions are set from the Database Role Editor or the Cube Role Editor in Analysis Manager, any missing information is added by the Role Editor. This information is not added when role permission rules are added programmatically.

Steps to Reproduce Behavior
 Create a new Microsoft Visual Basic application, and add a reference to Microsoft Decision Support Objects. Remove the default form and add a module.  Add the following code to the Visual Basic module: Const ServerName = &quot;localhost&quot; '&quot;SERVER-NAME&quot; Const DatabaseName = &quot;Foodmart 2000&quot; '&quot;OLAP-DATABASE-NAME&quot; Const UserList = &quot;USERS&quot; '&quot;DOMAIN-NAME\USER-ID;DOMAIN-NAME\USER-ID&quot; Const CubeName = &quot;HR&quot; '&quot;CUBE-NAME&quot; Sub Main

Dim dsoServer As DSO.Server Dim dsoRole As DSO.Role Dim dsoCubeRole As DSO.Role Dim dsoDB As DSO.Database Dim RoleName As String RoleName = &quot;Fred&quot; Set dsoServer = New DSO.Server 'Connect to server. dsoServer.Connect ServerName 'Connect to database. Set dsoDB = dsoServer.MDStores.Item(DatabaseName) If dsoDB.Roles.Find(RoleName) Then dsoDB.Roles.Remove (RoleName) End If   Set dsoRole = dsoDB.Roles.AddNew(RoleName, sbclsRegular) ' 0) ' sbclsRegular    'Set role description.    dsoRole.Description = &quot;Test through Visual Basic application&quot;    dsoRole.LockObject 1, &quot;Creating Role&quot;    'Set Enforcement Location permission key.    dsoRole.SetPermissions &quot;EnforcementLocation&quot;, &quot;Server&quot;    'Add group of users or single user to roles.    dsoRole.UsersList = UserList    Dim strAllowedSet As String    Dim strDimensionSecurity As String    'Set Permission on Dimensions Time.    strAllowedSet = &quot;[Time].[1997].[Q1]:[Time].[1998].[Q4]&quot;    strDimensionSecurity = &quot;&quot;    strDimensionSecurity = strDimensionSecurity & &quot;&quot; dsoRole.SetPermissions &quot;Dimension:Time&quot;, strDimensionSecurity 'Set Permission on Dimension Store. strAllowedSet = &quot;[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[WA],[Store].[All Stores].[USA].[OR]&quot; strDimensionSecurity = &quot;&quot; strDimensionSecurity = strDimensionSecurity & &quot;&quot; dsoRole.SetPermissions &quot;Dimension:Store&quot;, strDimensionSecurity 'Deny access to other dimensions. For i = 1 To dsoDB.Dimensions.Count If dsoDB.Dimensions(i).Name <> &quot;Time&quot; Then If dsoDB.Dimensions(i).Name <> &quot;Store&quot; Then ' Pay Type is a private dimension in the HR cube. ' Setting permissions at the Database Level ' so preface it with the name of the cube and ^. If dsoDB.Dimensions(i).Name <> &quot;HR^Pay Type&quot; Then If dsoDB.Dimensions(i).Name <> &quot;Store Type&quot; Then If dsoDB.Dimensions(i).Name <> &quot;Measures&quot; Then

' Comment these two lines, because they contain an incomplete permission statement. strDimensionSecurity = &quot;&quot; strDimensionSecurity = strDimensionSecurity & &quot;</MEMBERSECURITY>&quot;

' Uncomment these lines, containing a complete permission statement, to apply the security rules. 'strDimensionSecurity = &quot;&quot; 'strDimensionSecurity = strDimensionSecurity & &quot;<PERMISSION Access=&quot;&quot;Read&quot;&quot; &quot; 'strDimensionSecurity = strDimensionSecurity & &quot;UpperLevel=&quot;&quot;[&quot; & Trim(dsoDB.Dimensions(i).Name) & &quot;].Levels(0)&quot;&quot; &quot; 'strDimensionSecurity = strDimensionSecurity & &quot;LowerLevel=&quot;&quot;[&quot; & Trim(dsoDB.Dimensions(i).Name) & &quot;].Levels(0)&quot;&quot; &quot; 'strDimensionSecurity = strDimensionSecurity & &quot;AllowedSet=&quot;&quot;{[&quot; & Trim(dsoDB.Dimensions(i).Name) & &quot;].Levels(0).Members(0)}&quot;&quot;/> &quot; 'strDimensionSecurity = strDimensionSecurity & &quot;</MEMBERSECURITY>&quot;

dsoRole.SetPermissions &quot;Dimension:&quot; & dsoDB.Dimensions(i).Name, strDimensionSecurity End If                End If              End If           End If        End If    Next 'Unlock the database. dsoRole.UnlockObject 'Update the Role. dsoRole.Update Dim dsoCube As DSO.Cube Set dsoCube = dsoDB.MDStores(CubeName)

dsoCube.LockObject 1, &quot;Creating New Roles&quot; Set dsoCubeRole = dsoCube.Roles.AddNew(RoleName) strDimensionSecurity = &quot;&quot; strDimensionSecurity = strDimensionSecurity & &quot;<PERMISSION Access=&quot;&quot;Read&quot;&quot;&quot; strDimensionSecurity = strDimensionSecurity & &quot; AllowedSet=&quot;&quot;{[Measures].[Org Salary],[Measures].[Count],[Measures].[Number of Employees]}&quot;&quot;&quot; strDimensionSecurity = strDimensionSecurity & &quot; /></MEMBERSECURITY>&quot; dsoCubeRole.SetPermissions &quot;Dimension:Measures&quot;, strDimensionSecurity dsoCube.Update

dsoCube.UnlockObject Set dsoCubeRole = Nothing Set dsoCube = Nothing dsoDB.Update dsoServer.Update dsoServer.UnlockAllObjects dsoServer.CloseServer Set dsoServer = Nothing Set dsoDB = Nothing Set dsoRole = Nothing MsgBox &quot;Done&quot; End Sub </li> Save, and then run the code. Open Analysis Manager and test the role.</li> Comment out the lines that contain the incomplete permission statement and uncomment the lines that contain the complete permission statement.</li> Save and re-run the code. Open Analysis Manager and test the role again.</li></ol>

Additional query words: Dimension Role Rule Permission OLAP change code

Keywords: kbinfo KB293815

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.