x

Automatically Export Cube Permissions to XMLA Script

I want to automatically export all of my cube permissions (users/roles) to an XMLA script (Like it is impossible in SQL Management Studio via the Script Role as for single roles).

Is there a way to automatically (eg. via a sql job) create such a xmla script wich contains the security information or is there another way to backup the security settings?

I need to ensure to automatically backup these security information before deploying the SSAS project (so that the roles/users won't be overwritten/deleted unintended)

more ▼

asked Aug 19, 2011 at 02:09 AM in Default

avatar image

hobe
13 1 1 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You can use following steps to backup all the security info:

  1. Create ALTER DATABASE script - Right click on the SSAD database, choose Script Database AS -> ALTER To

  2. Once you have the script, you can easily modify the script by removing everything from the <Database> node under <ObjectDefinition> node except the <Roles> and <DatabasePermissions> sub-nodes.

After those changes you will have script like the one below, which when executed restores the uses and permissions in case of problems. :-)

So Generally you can simply Script the whole DB and when necessary you simply update the script for restoring the Roles, Role Members and Permissions.

 <Batch Transaction="false" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <Alter AllowCreate="true" ObjectExpansion="ExpandFull">
     <Object>
       <DatabaseID>MySSASDatabase</DatabaseID>
     </Object>
     <ObjectDefinition>
       <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
         <ID>MySSASDatabase</ID>
         <Name>MySSASDatabase</Name>
         <Roles>
           <Role>
             <ID>MyTestRole</ID>
             <Name>MyTestRole</Name>
             <Members>
               <Member>
                 <Name>SomeUser</Name>
                 <Sid>S-1-5-21-1362132055-2781161361-749619117-1005</Sid>
               </Member>
             </Members>
           </Role>
         </Roles>
         <DatabasePermissions>
           <DatabasePermission>
             <ID>DatabasePermission</ID>
             <Name>DatabasePermission</Name>
             <RoleID>MyTestRole</RoleID>
             <Process>true</Process>
             <ReadDefinition>Allowed</ReadDefinition>
             <Read>Allowed</Read>
             <Administer>true</Administer>
           </DatabasePermission>
         </DatabasePermissions>
       </Database>
     </ObjectDefinition>
   </Alter>
 </Batch>
more ▼

answered Aug 19, 2011 at 04:08 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Works, but ObjectExpansion attribute must set to "ObjectProperties", otherwise the script will overwrite the whole analysis database (db would be emtpy and only contain the roles (cubes, dimensions, dsv, ...) would be be deleted)

Aug 22, 2011 at 04:52 AM hobe
(comments are locked)
10|1200 characters needed characters left

What about the Tabular Model? I've tried this and I get the following error:

Error ErrorCode="3241541687" Description="Cannot set StorageEngineMode property of database 'Model_XXXX' to 'Traditional' because Analysis Services is in Tabular mode." Source="Microsoft SQL Server 2012 Analysis Services" HelpFile=""

more ▼

answered Oct 08, 2013 at 02:59 PM

avatar image

endymx
0

You might ask this as a new question rather than as an answer to an old question.

Oct 08, 2013 at 04:05 PM KenJ
(comments are locked)
10|1200 characters needed characters left

I have found a ridiculously easier method to script out an existing SSAS role. 1. Right Click on the Role 2. Click Duplicate... 3. Give the "new role" a name which will be used to recreate the role 4. Click Script > Script Action to New Query Window

This is your script that you can run after each cube redeployment. Easy, isn't it? :)

more ▼

answered Aug 13, 2015 at 11:21 PM

avatar image

gabitza777
0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x235
x196
x85
x6

asked: Aug 19, 2011 at 02:09 AM

Seen: 6349 times

Last Updated: Aug 13, 2015 at 11:21 PM

Copyright 2016 Redgate Software. Privacy Policy