SSAS: Cube Deployment Issues with Deployment Wizard addressed in SQL Server 2008
If you recently upgraded to SQL Server 2008 or considering an upgrade, you would be glad to know that the Deployment Wizard of Analysis Services works in the way it should (long overdue). In earlier versions, if a new version of the Analysis Service database was deployed over an one, immediately after deployment all the roles would have no permission to the cube even if “retain role and members” option is chosen. Not only that, the role definition especially Dimension Data and Cell Data Access permissions would be lost after deployment. The only saving grace was the role membership remained intact.
With 2008 version, if retain roles and members option is chosen during deployment,
- Roles retain their existing permission to cubes and mining structures after deployment. Existing permissions are not lost.
- Dimension Data and Cell Data permissions are preserved in the role definition after deployment.
- And Role membership stays put.
There is a gotcha though … if you script a role from SQL Server Management Studio as XMLA, the Dimension Data Permission and Cell Data Permission will not be scripted with the role definition. This is because as in previous versions, Dimension Data Permission is considered to be part of Dimension Definition and will be scripted as part of Dimension. Similarly Cell Data Permission is considered to be part of Cube definition and will be scripted as part of Cube.
