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.