Got a question? Call 1800 853 276   |   

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.

DDLS offers a number of PowerShell courses, from intro to advanced:10961 – Automating Administration with Windows PowerShell [B]10962 – Advanced Automated Administration with Windows PowerShell [A]55039 – Windows PowerShell Scripting and Toolmaking [A]

DDLS also offers courses on SQL Server 2014 BI products like SQL Server Reporting Services:20466 – Implementing Data Models and Reports with Microsoft SQL Server [C]20467 -Designing Self-Service Business Intelligence and Big Data Solutions

Back to the task at hand. 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 {$ -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 {$ -eq $rolename}

#Get the required roles persissions for reference. This will show what tasks the role can perform.$roletasks = $ssrs.GetRoleProperties($,$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!

Feature Articles

Our AIICT brand expands portfolio with ten new courses to help address ICT skills shortage
The Australian Institute of ICT (AIICT) has introduced a new series of industry certified bootcamp programs and nationally-recognised qualifications to meet the surging demand for skilled ICT professionals in Australia.  The bootcamps support the Morrison Government’s recently announced Digital Skills Organisation (DSO) pilot, which recognises the importance of non-accredited training to support the development of skills of the future workforce. The bootcamp programs run for six months and comprise of several vendor-specific certifications. The courses include ‘Cloud Computing Certified Professional’, ‘Certified Microsoft Full Stack Developer’, ‘Certified Artificial Intelligence Professional’, ‘Growth Marketing Professional’ and ‘Certified Project Management Professional’. The decision to introduce the bootcamps follows the VET sector’s increasing move away from nationally recognised qualifications to vendor-specific, industry-certified training. According to the National Centre for Vocational Education Research, preference for accredited training courses has declined steadily in recent years, with employers increasingly less satisfied that these courses provide their employees with the most relevant and important skills for their business. This has led many organisations to preference non-accredited training provided by private technology vendors such as Microsoft and AWS.