Microsoft KB Archive/933836

= Security permissions that are associated with the existing roles may reset when you use the Analysis Services Deployment Wizard to deploy a SQL Server 2005 Analysis Services project =

Article ID: 933836

Article Last Modified on 10/31/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



SYMPTOMS
You use the Analysis Services Deployment Wizard to deploy a Microsoft SQL Server 2005 Analysis Services (SSAS) project. If you select the Retain roles and members option on the Specify Options for Partitions and Roles page, security permissions that are associated with the existing roles reset to none.

Additionally, all the cube security settings that are defined by the roles and all the dimension security settings that are defined by the roles are lost.



CAUSE
This issue occurs because security permissions secure objects, and these objects contain the security permissions. Security permissions are not contained by the security roles with which the security permissions are associated. You cannot use the Retain roles and members option to maintain the existing security settings.



WORKAROUND
To maintain security settings after you run the Analysis Services Deployment Wizard, use one of the following methods.

Method 1
To deploy a master SSAS project to many destination servers, follow these steps.

Note This method assumes that each server differs only in the dimension data permission of the security role.
 * 1) In SQL Server Business Intelligence Development Studio, open the master project.
 * 2) Add roles to contain the permissions that the SSAS project requires.
 * 3) Configure permissions on the master project for each role.
 * 4) For each role, configure the membership to apply the permissions on each destination server. These permissions are required for the databases on each destination server.

Note If a destination database does not have to have a role, you must not configure the membership setting.
 * 1) Use the Analysis Services Deployment Wizard to deploy the master project.
 * 2) On the Specify Options for Partitions and Roles page, select the Deploy roles and retain members option.

Method 2
Use this method if the following conditions are true:
 * You have many SSAS projects for one database, and you want to deploy these projects to one or more destination servers.
 * The existing roles have complex security settings.

To restore the previous security settings for the database, follow these steps before you deploy the SSAS projects:  Open SQL Server Management Studio, and then connect to the destination SSAS server. In Object Explorer, right-click the role that you want to maintain, and then click Duplicate. In the New Role Name box, type a name, and then click Script.

A new query window appears in SQL Server Management Studio. Click Cancel to close the Analysis Services Duplicate Security Role dialog box. In the new query window, remove the first  XML tag and all the information that is included in the first  XML tag.

Note The information in the first  XML tag in the script creates a new role.</li> Replace the information in all the <RoleID> tags with the role ID of the existing role that you want to maintain.

Note To obtain the role ID of the existing role, follow these steps: <ol style="list-style-type: lower-alpha;"> Right-click the role, point to Script Role as, point to Create To, and then click New Query Editor Window. A new query window appears.</li> In the new query window, notice the value of the information that is included in the <ID> XML tags.</li></ol> </li> Save the script.

After you deploy the SSAS project, you can run the script to restore the security settings.</li> Repeat steps 1-7 for each role.</li> Use the Analysis Services Deployment Wizard to deploy the master project.</li> On the Specify Options for Partitions and Roles page, select the Deploy roles and retain members option.</li> Run the scripts that you saved in step 7 to restore the security settings.

Note If objects that the security settings secure have been removed or if the objects are significantly changed, you must manually modify the scripts to reflect the changes.</li></ol>

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
The Analysis Services Deployment Wizard uses the XML for Analysis (XMLA) protocol to deploy the metadata of an SSAS project to a destination server. The XMLA protocol handles the output files that are generated by the SSAS project.

The Analysis Services Deployment Wizard provides three options that maintain certain security settings on the destination server. The following table lists these options and the behavior that occurs when you select these options.

When you use Analysis Management Objects (AMO) to create roles or other objects, we recommend that you provide the ID of the role and the name of role if you do not create many roles. For example, provide the ID of the role and the name of the role when you want to create fewer than 100 roles.

Keywords: kbtshoot kbexpertiseadvanced kbsql2005bi kbsql2005as kbprb kbinfo KB933836

-

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

© Microsoft Corporation. All rights reserved.