Error while deleting a role

Question

I am trying to delete a role from SSAS dabase via XML but am getting the following error. Its a dummy role and does not have any permissions related to it.

Can someone help please? I am using the following code:

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <RoleID>Role 13</RoleID>
        <DatabaseID>esaq012b</DatabaseID>
    </Object>
</Delete>

Thanks!

 

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.AnalysisServices.OperationException: Errors in the metadata manager. The database permission with the ID of 'DatabasePermission 13', Name of 'DatabasePermission 13' was invalidated by operations in the transaction. Errors in the metadata manager. The transaction cannot be committed because one or more objects were invalidated during the transaction.      at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfError)     at Microsoft.AnalysisServices.AnalysisServicesClient.Delete(IMajorObject obj, ImpactDetailCollection impact, Boolean ignoreFailures)     at Microsoft.AnalysisServices.Server.Delete(IMajorObject obj, ImpactDetailCollection impactResult, Boolean ignoreFailures)     at Microsoft.AnalysisServices.MajorObject.DropPrivate(DropOptions options, XmlaWarningCollectio

Answer 1

Got it working!

Apparently, when we ask SSAS to create a role, it creates a role AND A PERMISSION ALONG WITH IT. And after creating the role, if we want to delete it, we have delete this permission forst and the  delete the role. The following gets executed when we create a new role using the UI.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>esap011a</DatabaseID>
    </ParentObject>
    <ObjectDefinition>
        <Role 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">
            <ID>Role 13</ID>
            <Name>SynchDB</Name>
        </Role>
    </ObjectDefinition>
</Create>

 

Alternatively if we replace the default ID ("Role 13" with a custom ID as shown below), the deletion happens properly (i.e) No permission is getting created by default and this role is not dependent on any permission!

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>esap011a</DatabaseID>
    </ParentObject>
    <ObjectDefinition>
        <Role 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">
            <ID>SyncDB</ID>
            <Name>SynchDB</Name>
        </Role>
    </ObjectDefinition>
</Create>

Do let us know if we have a better solution for this!

 

Answer 2

I don't think there is any better solution for this. When you use the Role dialog in BIDS or SSMS there is a lot of work that goes on behind the scenes. For a single role SSAS can create related DatabasePermission, CubePermission, DimensionPermission and CubeDimensionPermission objects. You have to remove any of these related objects that exist before removing the role itself. All of these permission objects get stored with their respective objects, so if you secure a geography dimension so that a role can only see certain countries then a DimensionPermission object is created under the dimension that links back to the role.

If you right click on a role in Management Studio, choose the delete option and then instead of clicking OK, click on the script button you will see that SSMS will generate a <Batch> that deletes all the related permission objects.

---

http://geekswithblogs.net/darrengosbell - please mark correct answers

Answer 3

Thanks Darren!

Any explanation on why the permisisons objects are not created when we create a role with a custom "id"

Answer 4

It depends on how you created your role. The GUI should create these objects for you. If you are creating a role via AMO then you would need to add the DatabasePermission and CubePermission objects in order to create a functioning role.

---

http://geekswithblogs.net/darrengosbell - please mark correct answers