Windows Management And Scripting BlogA wealth of tutorials on various Windows Operating Systems related topics like setting up Windows 7/2003/2008/Azure SQL Server 2005,2008 and Azure, troubleshooting and more.

Subscribe via email

Grab our RSS Feed

How to manage permissions in SQL Server Reporting Services

SQL Server Reporting Services (SSRS) is an extremely handy tool in the SQL Server 2005 suite. You can use it to easily initiation, deploy and manage intelligence for your entire organization. Its simplicity, even if, can lead to a less then optimal configuration. You set it up, toss in some intelligence and walk away. The problem is that you really don’t know who can see your intelligence. How can you manage permissions in SQL Server Reporting Services to ensure that sensitive intelligence are only seen by the public authorized to view them?

Because much of a company’s data is sensitive and must not be seen by everyone, data safeguard is a priority. In this tip, we will cover the SSRS security model and talk in this area how you can leverage it to lock down your SSRS environment.

Overview

SQL Server Reporting Services security is managed on two levels: the SSRS site and on items within the site. At each level, the tasks that users can go are managed via roles. Roles are just groups with particular tasks assigned to them and members of the role can go the assigned tasks. It’s that simple. Taking some time to initiation appropriate roles and assigning users accordingly will ensure that your SSRS site, and the intelligence it houses, are secure.

Site-level security in Reporting Services 2005

The first level of security is site-level security. On the SSRS site as a total, you can manage the tasks your users are allowable to go. The tasks are fixed, and you need to initiation your roles with these fixed tasks in mind.

At this level, you will be assigning “administrative” tasks to users. Standard users that just need to view intelligence will probably not require any permissions at this level of SQL Server Reporting Services, beyond the ability to view properties and schedules and do report definitions. Here are the tasks you can assign to roles at the site level:

• Do Report Definitions allows a user to run a report definition without first loading the definition onto the SSRS server. This is vital if you want your users to run report definitions from applications separate from SSRS, such as the Report Builder.
• Breed Events allows applications to breed events in the SSRS namespace.
• Manage Jobs permits users to view the jobs that are running on your SSRS server and cancel them if necessary.
• Manage Report Server Properties is a task that allows users to manage properties of the report server, as well as the items managed by the report server.
• Manage Report Server Security allows users to view and modify the members of your system-level roles.
• Manage Roles permits users to initiation, view and modify the role definitions. These users can change the tasks that are assigned to your roles.
• Manage Shared Schedules. SSRS contains shared schedules that can be tied to report execution; this task will allow users to manage these schedules.
• View Report Server Properties lets your users view, but not change, the properties of the report server. This task is implied if the role has the Manage Report Server Properties task assigned.

• View Shared Schedules lets users view, but not change, the shared schedules on the report server. This task is implied if the role has the Manage Shared Schedules task assigned.

To control which users are allowable to go each of these tasks, you first initiation a system-level role. There are two built in system-level roles when you install SQL Server Reporting Services.

• System Administrator. This system-level role manages all aspects of the SSRS site. The only task that cannot be performed by default is the Breed Events task. If you want administrators, or any user for that matter, to be able to go this task, you must explicitly assign it to the appropriate role.
• System User. These users are allowable to view report server properties and shared schedules and do report definitions. These tasks are assigned so that users can run intelligence.

Take a look at the screenshot below. To initiation new roles, click Site Settings in the top right corner of the Report Browser and then select Configure system-level role definitions under Security. This will open the System Roles page: To initiation a new role, click the New Role button. This will open the New System Role page, publicized in the screenshot. All you have to do now is name the role, give it a description, and then select all the tasks you want this role to be able to go. When you’re done, click OK.

To assign users to this newly made role, go back to the site settings screen and select Configure Site-wide Security. This will open the System Role Assignments screen and you can austerely click New Role Assignment to add new Windows users or groups to one of your SSRS system-level roles.

Item-level security in Reporting Services 2005

Item-level security is managed in much the same way as site-level security. You still work with roles and tasks, but the role assignment is done on a per-item basis. In other words, a user in the Browser role for one folder may be in the Content Manager role on another folder. Here are the tasks that can be assigned to item-level roles:

• Consume Intelligence allows users to read report definitions. This is a fancy way of saying “these users can run intelligence.”
• Initiation Associated Intelligence allows users to initiation associations between columns from one report to another. Users can also publish these intelligence to a folder.
• Manage All Subscriptions is a task that permits the user to view and manage other user’s subscriptions to an item.
• Manage Data Sources lets the user initiation and delete shared data sources in SSRS.
• Manage Folders allows users to initiation and delete folders in SSRS. They can also modify the properties of existing folders.
• Manage Individual Subscriptions permits the user to initiation, view and modify subscriptions the user owns.
• Manage Models is a task giving the user rights to initiation, view and modify models.
• Manage Intelligence allows users to initiation and delete intelligence.
• Manage Resources lets users initiation, modify and delete resources in a folder. Resources are items such as shared schedules.
• Set Security for Individual Items is a task that permits the user to manage security for intelligence, folders, resources and shared data sources.
• View Data Sources lets the user view the properties of shared data sources.
• View Folders allows a user to view folders and folder properties.
• View Models authorizes users to view models and model properties.
• View Intelligence lets a user view intelligence in the folder hierarchy. This does not, even if, allow users to run intelligence — for that they require the Consume Intelligence task.
• View Resources lets a user view resources and resource properties in folders.

As with system-level roles, there are some built-in item-level roles you can use when assigning permissions in this area of SQL Server Reporting Services. If these roles aren’t enough, you can build additional roles and assign users any amalgamation of the item-level tasks we just looked at. The built-in item-level roles are as follows:

• Browser. This role is configured to allow users to view folders and intelligence and lets them subscribe to intelligence.
• Content Manager. These users can manage the content of the SSRS site, including managing folders, intelligence and resources.
• My Intelligence. Users are allowable to publish intelligence and manage intelligence, folders and resources in their My Intelligence folder.
• Publisher. This user can publish intelligence and manage intelligence, folders and resources on the report server.
• Report Builder. Report Builders have permission to view report definitions.

Now, let’s get down to the details. To manage these roles, you have several options. Item-level security can be applied to a folder, report, data source or resource. To give SQL Server users permission to an item, you need to open that item and view its security properties. When you add a user, you also have to assign a user to a role for that item. In the case of folders, the role a user is assigned at the top-level folder will, by default, be inherited by other items inside that folder. You do have the ability to override security on a decrease-level folder of item-level security.

At this point, the security of your SSRS server is entirely up to you. You can initiation uncommon folders for each department and assign only employees in that department with access to that folder. Within each department folder, I like to initiation an additional folder for sensitive intelligence and further lock that folder down to the appropriate users. Take some time and really plot out how your intelligence will be placed on the server and how you want the security to look. When using SQL Server Reporting Services, there is no reason that all of your intelligence, regardless of sensitivity, can’t be stored in a single report server.

Related posts:

1. Administering SQL Server Reporting Services Using Sql Server Management Studio
2. SQL 2008 : Reporting Services Architecture
3. Microsoft SQL Server Reporting Services (SSRS)
4. SQL Server 2008 Reporting Services
5. SSRS Reporting Services & Architecture