Dan English's BI Blog

Had a great question last week at the MNPASS SQL Server User Group in regards to using the Analysis Services Deployment Wizard and I just wanted to post the follow-up.  I have read about the wizard, but actually have never used it (but you need to know about it for the MCTS test 70-445 or 70-448).  I have just always deployed my databases through BIDS and have always had the roles and permissions defined in the solution.  This provided me a great opportunity to explore the wizard.  What I needed to find out was if the issue of losing the database permissions had been fixed in 2008 (refer to this MS KB article for the current issue here – basically the permissions do not get pulled into the XMLA script using the ‘Retain roles and members. The roles and members will not be deployed.’ option).

So first I tested this out in 2005 to verify that this was an issue and there wasn’t a hidden checkbox somewhere to resolve the issue.  I generated the script and reviewed it and it did pull in the roles and members, but no permissions (database, dimensions, etc.).  So whatever permissions were setup on the server would be wiped out once the script was executed.  Here is a snippet of the script and if you performed a search in the file you would see that no permissions were included.

I then setup a project in BIDS 2008 (VS 2008) and performed the same steps as with 2005 and everything was pulled into the script properly (cube, database, and dimension permissions) this time

The one thing is though if you are trying to separate the security control and permissions from the developers and DBAs this really won’t fix that problem because the wizard needs to extract the permissions and include them in the file to be executed against the SSAS database.  This means that the developers will be able to view and modify the permissions prior to executing the script.  I suppose this would mean that the DBAs would need to take over the responsibilities of using the deployment wizard or at least have a backup script of the existing Roles setup on the server prior to deployment.