sql-server - 在 SSAS 安全中添加新用户

Question

我想使用以下 XMLA 脚本在 SSAS 安全性中添加用户。

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     

它工作正常并且能够在 SSAS 中添加新用户。但问题是，它还会删除所有现有用户。如何解决这个问题？我想要所有现有用户加上新用户。

Answer 1

如果安装 ASSP然后您可以运行以下调用 ASSP 存储过程的 SQL 查询以获取当前成员的列表。完成后，您可以将@oldMembers 附加到现有的 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