Select the product you need help with

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 - View products that this article applies to.

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.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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.

In SQL Server Business Intelligence Development Studio, open the master project.
Add roles to contain the permissions that the SSAS project requires.
Configure permissions on the master project for each role.
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.
Use the Analysis Services Deployment Wizard to deploy the master project.
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 <Alter> XML tag and all the information that is included in the first <Alter> XML tag.

Note The information in the first <Alter> XML tag in the script creates a new role.
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:
1. 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.
2. In the new query window, notice the value of the information that is included in the <ID> XML tags.
Save the script.

After you deploy the SSAS project, you can run the script to restore the security settings.
Repeat steps 1-7 for each role.
Use the Analysis Services Deployment Wizard to deploy the master project.
On the Specify Options for Partitions and Roles page, select the Deploy roles and retain members option.
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.

Method 3

Use the SQL Server 2008 version of the Analysis Services Deployment Wizard to deploy a project to a SQL Server 2005 Analysis Services engine.

Note In SQL Server 2008 Analysis Services, security object permissions that are associated with a role are retained by the Analysis Services Deployment Wizard if the Retain roles and members setting is checked. This behavior has changed from earlier versions of SQL Server Analysis Services.

Back to the top | Give Feedback

STATUS

This behavior is by design.

Back to the top | Give Feedback

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.

Collapse this tableExpand this table

Option	Result
Deploy roles and members	Any existing roles and members are replaced.
Deploy roles and retain members	Roles are deployed. New roles are deployed together with their members.
Retain roles and members	Roles and members are not deployed.

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.

Back to the top | Give Feedback