SSAS: Administrator role is not really admin!

I discovered this week an interesting bug in SSAS (both 2005 and 2008 versions). A user that was supposed to have full permission complained he can see only few members in a dimension. I checked his role and it really was defined as “Full control (Administrator)” (Checkbox in General tab of role properties). After short investigation I found that this role was in past with those limitations in members, and later the admin checkbox was selected to make this user admin on DB. So in Dimension Data tab settings were still saved,  and in fact they still were applied! I tested it in many cases and in both 2005 and 2008 versions: every time a role is edited to “Full control (administrator)”, it still applies settings of Dimension Data tab.

Someone might explain it by the famous “It’s not a bug, it’s a feature!”. Well, It might be nice to have an option that even admin user can limit access to members for any reason, but in this case when admin checkbox is selected, Dimension data tab becomes gray, and you cannot edit or even read it, for the simple reason that it’s not relevant now. Note that in Cubes tab there is no bug, and even if not all cubes have “read” value in Access column, admin user can browse any cube.

So the solution to my issue was to uncheck admin checkbox, select all members in this dimension attribute, and select checkbox back..

SSAS: How to copy roles between servers or databases

In SSAS there is no direct option or wizard to copy roles between different servers or databases. In this post I want to explain how we can copy one or more specific roles in few steps. It can be useful for example if your roles are created on production database, and you have a security issue with one on more roles, so you want to move them to test environment to check it.

Because this method moves every role separately, if you need to move all roles, it’s better to synchronize (or backup and restore) entire database. If you have different version of your DB on target, you can transfer database from source, and then redeploy the target solution with deployment wizard, using “Retain roles” option. Then you will get your target database but with roles from source.

Here are steps for moving one role:

1.       On source server, right-click on role and select “Duplicate…”.

2.       Enter a new name for role (for example “temp”).

3.       Script it to new window. Click on cancel and not on OK (we only need the script, not to do it!)

4.       Make replacements in script:

a.       <RoleID>Role</RoleID> to <RoleID>Your role Id</RoleID>, for example <RoleID>User1role</RoleID> (one or many occurrences). If you are not sure what is your source role ID, you can get it by scripting the role to create:

b.      <ID>Role</ID> to <ID>Your role Id</ID> (one occurrence)

c.       <Name>temp</Name> to <Name>Your role name</Name> (one occurrence).

d.      If you move roles between databases with different names, replace <DatabaseID>Source DB</DatabaseID> to <DatabaseID>Target DB</DatabaseID> (one or many occurrences).

5.       Run the script on target server.

That’s all for one role, do it for every role you want to transfer.