SQL Server Create role with permissions on SSAS using PowerShell

a9wyjsp7 于 4个月前发布在 Shell

关注(0)|答案(1)|浏览(67)

In SQL Server SSAS I want to do a db restore from server A to server B, where DB already exists on server B. I was wondering if it's possible to script database permissions, so I can later run the script on server B to apply the original settings.

I was playing around with AMO, but I have limited programming knowledge, so not entirely sure how it works.

I know I can access this information:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServerName = 'serverNameA\PRD'
$SSASServer.Connect($SSASServerName)
$SSASServer.Version

$DBName = 'DB_A_PRD'
$SSASDatabase = $SSASServer.Databases.Item($DBName)
$SSASDatabase.Refresh()

so later I can get all details about specific role

foreach ($role in $SSASDatabase.Roles) {Write-Host $role.Name}
$SSASDatabase.DatabasePermissions.FindByRole($role.ID)

Now I can recreate role on the server B

$roleToCreate = new-Object([Microsoft.AnalysisServices.Role])($var)
$SSASDatabase.Roles.Add($roleToCreate)
$roleToCreate.Update()

And here's where I am lost. How should I properly assign Administer, or Process permissions to this $roleToCreate?

I've seen another user who applied Read settings this way, but I did not find way do to the same for Administer or Process permissions:

$dbperm = $SSASDatabase.DatabasePermissions.Add($roleToCreate.ID)    
$dbperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

From what I've read here: https://learn.microsoft.com/en-us/analysis-services/amo/programming-amo-security-objects?view=sql-analysis-services-2022 it is possible, but I am not sure how to make it work in PowerShell.

I cannot do it as $dbpermissions.Process = True, ps won't recognize it. I imagine I need to do something similar as with enums with Read rights, but I don't know how.

I was using this documentation for refrence: https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices?view=analysisservices-dotnet

sql-server

来源：https://stackoverflow.com/questions/72415867/create-role-with-permissions-on-ssas-using-powershell

1条答案

按热度按时间

uwopmtnx1#

I had difficulty adding roles with $SSASDatabase.Roles.Add($roleToCreate) . It would log a 0 to the terminal, indicating that nothing had been added.

I decided to move on and found a different approach with TMSL. This solution creates a new role, but you can also amend it to createOrReplace.

$tabularName = "tableName"
$sqlServer = "ssasServer"

$json = @"
{   
    "create":{   
       "parentObject":{   
          "database":"$($tabularName)"  
       },  
       "role":{  
          "name":"NewRoleName",  
          "modelPermission":"read",
          "members":[   
             {  
                "memberName": "domain\\username"
             }
          ]
       }  
    }  
 }
"@

$connectionString = "Data Source=$sqlServer;Initial Catalog=$tabularName"
Invoke-ASCmd -ConnectionString $connectionString -Query $json

Useful docs

https://learn.microsoft.com/en-us/analysis-services/tmsl/roles-object-tmsl?view=asallproducts-allversions

https://learn.microsoft.com/en-us/analysis-services/tmsl/createorreplace-command-tmsl?view=asallproducts-allversions

赞(0）回复(0）举报 4个月前