by Eric Johnson
SQL Server Reporting Services (SSRS) is an extremely handy tool in the SQL Server 2005 suite. You can use it to easily create, deploy, and manage reports for your entire organization. Its simplicity, however, can easily lead to a less then optimal configuration. You set it up, toss some reports on it and walk away. The problem is that you really don’t know who can see your reports. Data is extremely important to companies these days and much of that data is sensitive and should not be seen by everyone. So how can you implement SSRS in your environment and make sure that sensitive reports are only seen by the people authorized to view them? In this tip, we will cover the SSRS security model and talk about how you can leverage it to lock down your SSRS environment.
Overview
SSRS security is managed on two levels; the SSRS site and on items within the site. At each level, the tasks that users can perform are managed via roles. Roles are just groups with certain tasks assigned to that group and members of the role can perform the assigned tasks. It’s that simple. Taking some time to create appropriate roles and assign your user accordingly will ensure that your SSRS site, and the reports it houses, are secure.
Site Level Security
The first level of security is Site Level security. On the SSRS site as a whole you can manage the tasks that your users are allowed to perform. The tasks are fixed, and you need to create 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 reports will probably not require any permission at this level, beyond the ability to view properties and schedules and execute report definitions. Here are the tasks you can assign to roles at the site level.
Execute Report Definitions This task allows a user to run a report definition without first loading the definition on to the SSRS server. This is required if you want your users to run report definitions from applications separate from SSRS, such as the Report Builder.
Generate events Allows applications to generate events in the SSRS namespace.
Manage jobs Allows users to view the jobs that are running on your SSRS server and cancel them if need be.
Manage report server properties This task allows users to manage properties of the report server, as well as the items managed by the report server.
Manage report server security This task allows your users to view and modify the members of your system level roles.
Manage roles Users with the manage roles task are allowed to create, 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 This task lets your users view, be 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 This task lets your users view, be 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 allowed to perform each of these tasks, you first create a system level role. There are two built in system level roles when you install SSRS.
System Administrator Can manage all aspects of the SSRS site. The only tasks that cannot be performed by default is the Generate Events task. If you want administrators, or any user for that matter, to be able to perform this task, you must explicitly assign it to the appropriate role.
System User These users are allowed to view report server properties and shared schedules and execute report definitions. These tasks are assigned so that users can run reports.
To create new roles, click Site Settings in the top left corner of the Report Browser and then select Configure system-level role definitions under Security. This will open the System Roles page: to create a new role click the New Role button. This will open the New System Role page, shown below. All you have to do now is name the role, give it a description, and select all the tasks you want this role to be able to perform. When you’re done, click OK.
To assign users to your newly created 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 simply click New Role Assignment to add new Windows users or groups to one of your SSRS System Level Roles.
Item Level Security
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 reports Allows users to read report definitions. This is a fancy way to say “these users can run reports”.
Create linked reports Allows user to create links between columns in a report and another report. Users are also allowed to publish these reports to a folder.
Manage all subscriptions This task allows the user to view and manage other user’s subscriptions to an item.
Manage data sources Allows the user to create and delete shared data sources in SSRS.
Manage folders Allows the user to create and delete folders in SSRS. They can also modify the properties of existing folders.
Manage individual subscriptions This allows the user to create, view, and modify subscriptions that that user owns.
Manage models This task gives the user the rights to create, view, and modify models.
Manage reports Allows the user to create and delete reports.
Manage resources Allows users to create, modify, and delete resources in a folder. Resources are items such as shared schedules.
Set security for individual items This allows the user to manage security for reports, folders, resources, and shared data sources.
View data sources Allows user to view the properties of shared data sources.
View folders Allows user to view folders and folder properties
View models Allows users to view models and model properties
View reports This allows users to view reports in the folder hierarchy. This does not, however, allows users to run reports, for that they require the Consume Reports task.
View resources This task allows user to view resources and resources’ properties in folders.
As with system level roles, there are some built-in item level roles you can use when assigning permissions. If these roles aren’t enough, you can build additional roles, and assign users any combination 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 reports and allows them to subscribe to reports.
Content Manager These users can manage the content of the SSRS site. This includes managing folders, reports, and resources.
My Reports This role allows users to publish reports and manage reports, folders, and resources in their My Reports folder.
Publisher This user can publish reports and manage reports, folders, and resources on the report server.
Report Builder Report Builders have access to view report definitions.
Okay, now 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 users permission to an item, you need to open that item and view its security properties. When you add a user, you will also need 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 lower level folder of item.
At this point, the security of your SSRS server is entirely up to you. You can create different folders for each department and assign only employees in that department with access to that folder. Within each department folder, I like to create an additional folder for sensitive reports and further lock that folder down to the appropriate users. Take some time and really plan out how your reports will be placed on the server and how you want the security to look. Using SSRS there is no reason that all your reports, regardless of sensitivity can’t be stored in a single report server.
Copyright 2000 by TechTarget.com, 117 Kendrick Street, Suite 800, Needham, MA 02494. Reprinted by permission of TechTarget.com.
Digg It