I needed to delve into Powershell again recently as a means to automate the creation of a long list of roles and the addition of AD groups to them.
Since there were so many i thought it would be easier to have a list of roles in a text file and just parse that to create all your roles automatically. The script posted here is a first pass that was used during development and does the following:
- iterate through a text file containing a list of roles.
- For each line found:
- check if the role exists and then delete it. (the script caters for an initial role creation so the delete was more for testing)
- Create a new role object from the current line in the source text
- Add role to SSAS database
- Add database permission for role
- Add cube permissions for role to all cubes in database
- Associated an AD group and allowed set with the role – my example uses [Organisation].[Cost Centre Code] where the member names are the same as the role names. Another assumption for is that the AD group has the same name as the role. One could extend this script easily to parse a CSV for more complex requirements)
- log any errors found when adding new objects to the database
I got the basic code from Darren’s post and made a bunch of changes to suit my requirements. So here it is….