THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

SSAS and role deployment

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.

  1. Create a SSAS project and name it BugRoles (we will deploy it into BugRoles database)
  2. Create a role TestRole into a SSAS cube
  3. Assign a user (DOMAIN\UserA) to role TestRole
  4. Deploy SSAS project to the SSAS server
  5. Rename DOMAIN\UserA into DOMAIN\UserB on domain controller
  6. If you modify dimensions or cubes deploy and then deploy the project overwriting the existing database you created at step 3, it still works
  7. 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 DOMAIN\UserA 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.

Published Wednesday, January 25, 2006 6:41 PM by Marco Russo (SQLBI)



Angelo Amoruso said:

I'd like to kindly thank you for this post.

I was banging my head on a wall when this error occured and I wasn't able to understand what to do, feeling hopeless at best.

It occured on a SSAS 2008 R2 installation; even if upgraded to SP3 (latest available) it didn't go away at all.

When will Microsoft learn to handle such issues the right way?

It's just programming 101 to always handle an error with a meaningful message.

Thank you.

January 28, 2015 12:42 PM

Marco Russo (SQLBI) said:

I know... thanks for the feedback! :-)

January 28, 2015 12:47 PM

Walter Weber said:

So how did you resolve the issue?  Did you find a solution by any chance...I am really hoping here.  Thanks for the write up.  I have a design with multiple cubes and due to security requirements have to create a large number of roles.  Everything will run fine for a bit than I run into this error and have to modify the code files in order to fix and in may cases land up having to remove almost all of the roles and then rebuild them

May 9, 2016 10:30 AM

Marco Russo (SQLBI) said:

As far as I know, there are no news on that. You should modify the deployment or roles writing a Power Shell script that manages error handling.

May 9, 2016 1:02 PM

Thomas Höfer, Vienna said:

I also like to kindly thank you for this post. It worked for me as well!

May 23, 2017 5:34 AM

Marco Russo (SQLBI) said:


May 28, 2017 6:23 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement