我想使用以下 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 中添加新用户。但问题是,它也会删除所有现有用户。如何解决这个问题?我希望拥有所有现有用户以及新用户。
如果你安装ASSP然后您可以运行以下 SQL 查询,该查询调用 ASSP 存储过程来获取当前成员的列表。完成此操作后,您可以将 @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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)