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

hobe gravatar image

hobe
13 1 1 2

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

2 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

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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

endymx gravatar 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
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x193
x172
x69
x6

asked: Aug 19, 2011 at 02:09 AM

Seen: 3860 times

Last Updated: Oct 08, 2013 at 04:05 PM