asked
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)
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 `` node under `` node except the `` and `` 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.
MySSASDatabase MySSASDatabase MyTestRole MyTestRole SomeUser S-1-5-21-1362132055-2781161361-749619117-1005 DatabasePermission DatabasePermission MyTestRole true Allowed Allowed true
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=""
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? :)
3 People are following this question.
