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 to share