sqlssasxmla

Creating a role in SSAS

I have a role that has been working fine for a while. The only user added to that role was a local user set up on the server called Cube. However all of a sudden the membername for that user against the role is the SID not the value LocalServer\Cube. When i manually remove the broken user from the role and re-add LocalServer\Cube this fixes the problem. However right-click - alter to on the role and set the value in the script, to LocalServer\Cube, it doesn't fix the issue. 

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
    <RoleID>Role 80</RoleID>
    <DatabaseID>OLAPCubes</DatabaseID>
</Object>
<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" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
        <ID>Role 80</ID>
        <Name>Cube</Name>
        <Members>
            <Member>
                <Name>LocalServer\Cube</Name>
                <Sid>S-1-5-21-3067301217-1800892027-3952499395-1116</Sid>
            </Member>
        </Members>
    </Role>
</ObjectDefinition>

Do you know why this might be? There are two issues here, what would cause it to change in the first place and can i create a script to change this again should it happen again.

Solution

  • I believe this is a problem with the SID not getting translated and not displaying the friendly name, most likely caused by a network issue because it's timing out when retrieving the friendly name for the SID from the Domain Controller . Perhaps you can try to add them to "Allowed RODC Password Replication Group" if you have access . Debugging this might require you to analyze the network trace .

    Please check below link for more info : https://blogs.technet.microsoft.com/askds/2011/07/28/troubleshooting-sid-translation-failures-from-the-obvious-to-the-not-so-obvious/