Adding New Users to SSAS Security

I want to add users to SSAS security using the following XMLA script.

DECLARE @CreateUserInSsasXMLA VARCHAR(MAX) = 
        '<Batch AllowCreate="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">            
            <Alter ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
            <Object />
              <Object>
                <RoleID>Administrators</RoleID>
              </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>Administrators</ID>
                  <Name>Administrators</Name>
                  <Members>        
                    <Member>
                      <Name>'+@UserID+'</Name>
                    </Member>        
                  </Members>
                </Role>
              </ObjectDefinition>
            </Alter>
            </Batch>
            '
            IF  @UserID <> 'Dummy_User'
            BEGIN               
                BEGIN TRY           
                    EXEC (@CreateUserInSsasXMLA) At AdminCPM;
                    PRINT 'User ' + @UserID + ' has been created.'
                END TRY
                BEGIN CATCH
                    PRINT 'Something went wrong while creating user ' + @UserID
                END CATCH
            END
            ELSE
            BEGIN
                PRINT 'Either Server Name or User Name or both parameter not specified.'
            END     

      

It works great and is capable of adding a new user to SSAS. But the problem is that it also removes all existing users. How to fix it? I want all existing users to add a new one.

+3


source to share


1 answer


If you install ASSP , you can run the following SQL query, which calls ASSP sproc to get a list of the current members. Once this is complete, you can add @oldMembers to the existing XMLA:



declare @members table (Sid varchar(1000), Name varchar(1000), Parent_RoleID varchar(1000), Parent_DatabaseID varchar(1000))

insert @members
EXECUTE('CALL ASSP.DiscoverXmlMetadata("\Database\Roles\Role\Members\Member")') at AdminCPM

declare @oldMembers varchar(max) = ''
select @oldMembers = @oldMembers + '<Member><Name>'+Name+'</Name></Member>
'
from @members

      

0


source







All Articles