A few days ago I have encountered a system error deploying a SSAS project that involves role member. The error message is too generic and it hides the real cause of the problem that is very hard to find.
The problem itself is really simple: if you try to deploy a SSAS project with a role object which contains a member name (in collection members) that doesn’t exists (or can’t be resolved) by SSAS server.
As you can imagine, the common case that really could bring you made is when you successfully deploy a project into a development server but you fail to deploy the same database into a production server. I’d like to share the repro case given by a real-world situation that produced this error.
- Create a SSAS project and name it BugRoles (we will deploy it into BugRoles database)
- Create a role TestRole into a SSAS cube
- Assign a user (DOMAINUserA) to role TestRole
- Deploy SSAS project to the SSAS server
- Rename DOMAINUserA into DOMAINUserB on domain controller
- If you modify dimensions or cubes deploy and then deploy the project overwriting the existing database you created at step 3, it still works
- Now if you delete BugRoles database from SSAS server and then deploy the project, you get a generic system error that doesn’t help you to understand that the wrong name DOMAINUserA into role TestRole is the real problem.
It’s interesting to note that furter deployments at step 6 works well because when the role is deployed, the member user name is converted into a SID (security ID) that is invariant from the login name: when you rename a user into Windows, his SID remains the same. A role (as any other SSAS object) is deployed to a server only if it has been changed from the previous deployment. For this reason only a change to the role object into the project or a deletion of the role (or of the whole database) from the server cause a new deployment of the role, that fails because the role member name can’t be resolved by SSAS server.
I already posted this bug through official channels, but since I lost 2 hours to identify the reason of a deployment error in this situaton, I think that sharing this knowledge could help someone else until MS will change the error message for this condition.
Originally appeared on: http://sqlblog.com/blogs/marco_russo/archive/2006/01/25/ssas-and-role-deployment.aspx