Automatically Maintaining SQL Server Reporting Services Folder Permissions Using PowerShell

I needed to automate setting the permissions of a “Sales” folder on our SQL Server 2012 Reporting Services.

The Sales staff within our organization need to be assigned only the “Browser” permission to the folder. They should not have permission to any other subfolder on the server.

We did not have any security groups in Active directory that I could use to simplify this.Instead, I found we had the sales people usernames defined in our Microsoft CRM system.So all I had to do was write a query to get the sales people, and then find a way to update the SSRS server.

Once again, PowerShell to the rescue. If you are going to learn a new thing this year, learn PowerShell.

In this example I’m using PowerShell to connect to the SSRS WebService, call various methods, manipulate objects on the server, and even create new objects based on a SSRS “Policy” class. I’ve added the PowerShell code to a SQL Server Agent job, and scheduled it to run nightly in conjunction with our data analysis maintenance.

First a bit of background and terminology: SSRS permissions are comprised of Tasks, Roles, Role Assignments, and Policies.

• A task is a permission to perform an action. e.g. View Reports

• A role is a collection of tasks. e.g. The Browser role has several permissions that allow viewing reports, folders, definitions, etc.

• A role assignment is a user or group being given a role. e.g. DDLS\Rboxall is given the Browser role.

• A policy is a role assignment that belongs to an item. e.g. This is what you see on the security tab when managing a report or folder.

All staff in the organization have role assignments in the “Home” folder, and these are inherited by the “Sales” subfolder.

So the code needs to stop the inheritance, but keep the administrative role assignments, and then add appropriate role assignments for each one of the sales staff.

1. Connect to the SQL Server database to get the user logins. You could also use the Activedirectory module cmd-lets to get them if you want.

2. Create a connection to the SSRS Webservice

3. Create a customized role if required. My code uses the existing “Browser” role.

4. Revert the target folder to inherited role assignments.

5. Remove any role assignments that should no longer be inherited.

6. Add a role assignment for each login.

Here’s the PowerShell code:

#Declarations#SSRS Server$servername = “Localhost”$foldername = “/Sales”$roleName = “Browser”

#SQL Server$SQLServer = “Localhost”$Sqldatabase = “AdventureWorks”$Query = “Select LoginID from HumanResources.Employee”

#Get usernames from SQL Query$connectionString = “Data Source=$SQLServer;Initial Catalog=$Sqldatabase;Trusted_Connection=True;”$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection$connection.ConnectionString = $connectionString$command = $connection.CreateCommand()$command.CommandText = $Query$adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command$dataset = New-Object -TypeName System.Data.DataSet$numrows = $adapter.Fill($dataset)$logins = ($dataset.Tables[0])

#Create connection to SSRS Server$ReportServerUri = “http://$servername/ReportServer//ReportService2010.asmx?wsdl”$ssrs = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential

#Get the namespace for use in later steps. You will need this to create objects using the “Policy” class.$namespace = $ssrs.getType().namespace

#Get the security scopes for reference. This shows the difference between server (system) level and item (catalog) level permissions.$securityscopes = $ssrs.ListSecurityScopes()

#Get the system level permissions for reference. This shows the server level permissions for System Administrators & System Users.$systempolicies = $ssrs.GetSystemPolicies()

#The following eighteen lines are included if you want to added a new customized role to the server. e.g. “Power User”

#Get the task definitions for the Catalog security scope. This shows the individual permissions that can be assigned to a role.#Importantly, it also shows the TaskID GUIDs, which are important to have if you wish to create a customized role.$tasks = $ssrs.ListTasks(“Catalog”)

#Get the tasks that have “view” permissions only. You can change the filter to get whichever tasks you require.$roletasks = $tasks | where-object {$_.name -like ‘View *’}

#Get the TaskIDs for these tasks.$roletaskIDs = $roletasks | Select-Object -ExpandProperty taskID

#If the required role does not exist create it, adding the required tasks at the same time.#Get all the roles for the “Catalog” security scope$roles = $ssrs.ListRoles(“Catalog”,$null)$rolenames = $roles | select-object -ExpandProperty nameIf($roleNames -notcontains $rolename){$role = $ssrs.CreateRole($rolename,$rolename,$roletaskIDs)}

#Get the required role$role = $ssrs.ListRoles(“Catalog”,$null) | where-object {$_.name -eq $rolename}

#Get the required roles persissions for reference. This will show what tasks the role can perform.$roletasks = $ssrs.GetRoleProperties($salesrole.name,$null,[ref]$salesrole.Description)$roletasknames = $ssrs.ListTasks(“Catalog”) | where-object{$roletasks -contains $_.taskID}

#Revert the folder to inherited permissions to remove existing role assignments.#First find if it is already inheriting roles assignments by getting its policies. It will throw a warning if you revert it to inherited if it already is inheriting.$inherited = $true$itempolicies = $ssrs.GetPolicies($foldername,[ref]$inherited)if (-not $inherited){$ssrs.InheritParentSecurity($foldername)}

#Get the array of role assignments for the folder. The folder will only have inherited roles assignments at this point.$itempolicies = $ssrs.GetPolicies($foldername,[ref]$inherited)

#We originally gave the “Domain Users” group a role assignment in the “Home” folder, and this is being inherited by the subfolder.#Remove the entry for “Domain\Domain Users”, but keep all the others administrative role assignments.$itempolicies = $itempolicies | where-object {$_.GroupUserName -ne “Domain\Domain Users”}

#Foreach user, add a Role Assignment using the required role.$loginNames = $logins | select-object -ExpandProperty DomainNameforeach ($login in $loginnames) {#create the role assingment for the user by creating an object from the “Policy” class.$policy = New-Object ($namespace + “.policy”)$policy.GroupUserName = $login$policy.Roles = $role

#add the role assignment to the array of assignments$itempolicies += $policy}

#Update the folder with the new role assignments$ssrs.SetPolicies($foldername,$itempolicies)#Finished!