Managing Reports on the Report Server | MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Now that you have moved some of your reports to the Report Server, you may be thinking your job is about done, but it is just beginning. Now you need to manage the reports and supporting materials to ensure the reports can be utilized properly by your users.

Two of the biggest concerns when it comes to managing reports are security and performance. Reports containing sensitive data must be secured, so they are only accessed by the appropriate people. Reports must return information to users in a reasonable amount of time without putting undo stress on database resources. Fortunately, Reporting Services provides tools for managing both of these concerns. Security roles and item-level security give you extremely fine control over just who has access to each report and resource. Caching, snapshots, and history let you control how and when reports are executed.

Security

In Reporting Services, security was designed with both flexibility and ease of management in mind. Flexibility is provided by the fact that individual access rights can be assigned to each folder and to each item within a folder. An item is either a report or a resource. You can specify exactly who has rights to each item and exactly what those rights are. Ease of management is provided by security inheritance, security roles, and integration with Windows security. We begin our discussion with the last entry in this list.

Note

Remember, although we are creating and maintaining these role assignments using the Report Manager, the security rights apply to Reporting Services as a whole. No matter how you access folders and items—through the Report Manager or through the web service—these security rights are enforced.

Integration with Windows Security

Reporting Services does not maintain its own list of users and passwords. Instead, it depends entirely on integration with Windows security. When a user accesses either the Report Manager web application or the web service, that user must authenticate with the Report Server. In other words, the user must have a valid domain user name and password, or a local user name and password, to log on to the Report Server. Both the Report Manager web application and the web service are set up requiring integrated Windows authentication to ensure this logon takes place.

Note

If it is impossible for each report user to have their own credentials on the Report Server, it is possible to create your own custom security. You can create a security scheme such as forms-based security to enable the users to authenticate and access reports. This is discussed in detail in Chapter 12.

Once this logon occurs, Reporting Services utilizes the user name and the user’s group memberships to determine what rights the user possesses. The user can access only those folders and items they have rights to. In Report Manager, users do not even see the folders they cannot browse and reports they cannot run. There is no temptation for the user to try and figure out how to get into places they are not supposed to go, because they do not even know these places exist.

Local Administrator Privileges

In most cases, rights must be explicitly assigned to folders and items. One exception to this rule, however, is local administrator privileges. Any user who is a member of the local administrators group on the computer hosting the Report Server has content manager rights to all folders and all items. These automatic rights cannot be modified or removed.

Let’s look at the security page:

Open the Report Manager in your browser and navigate to the Home folder.
Select the Properties tab. You see the security page for the Home folder, as shown in Figure 10–16.

Figure 10–16: The security page for the Home folder

The Report Server maintains a security page for each item in the Report Catalog—every folder, every report, and every supporting item. The security page lists all the role assignments for an item. Each role assignment is made up of two things: a Windows user or group and a security role. The rights associated with the security role are assigned to the Windows user or group.

Initially, one role assignment is on the security page for each item. This entry assigns the Content Manager security role to the BUILTIN\Administrators group. This entry is a reminder that any user who is a member of the local administrators group has rights to manage the contents of this folder.

Note

You could delete the role assignment for BUILTIN\Administrators, and the members of the local administrators group would still have rights to manage the contents of this folder. These rights are hardwired into Reporting Services. The BUILTIN\Administrators assignment on the security page is, in most cases, just a reminder of the rights held by anyone in the local administrators group.

Tasks and Rights

You can perform a number of tasks in Reporting Services. Each task has a corresponding right to perform that task. For example, you can view reports. Therefore, a corresponding right exists to view reports. The tasks within Reporting Services are shown in Table 10–1.

Table 10–1: Security Tasks within Reporting Services
Task	Description
Consume reports	Read report definitions.
Create linked reports	Create linked reports and publish them to a folder.
Manage all subscriptions	View, modify, and delete any subscription, regardless of who owns the subscription.
Manage data sources	Create, modify, and delete shared data sources.
Manage folders	Create, view, and delete folders. View and modify folder properties.
Manage individual subscriptions	Create, view, modify, and delete your own subscriptions.
Manage models	Create, view, and delete models. Modify model properties.
Manage report history	Create, view, and delete report history snapshots. Modify report history properties.
Manage reports	Create, view, and delete reports. Modify report properties.
Manage resources	Create, modify, and delete resources. View and modify resource properties.
Set security for individual items	View and modify security settings for reports, folders, resources, and shared data sources.
View data sources	View shared data sources and their properties.
View folders	View folders and their properties.
View models	View models. Use models as report data sources. Query models for data.
View reports	View reports and linked reports along with their report history snapshots and properties.
View resources	View resources and their properties.

You are probably not familiar with some of these tasks. We discuss linked reports in the section “Linked Reports,” and we discuss report history snapshots and subscriptions in Chapter 11. For now, you simply need to know these are tasks with associated rights within Reporting Services.

In addition to the tasks listed in Table 10–1, there are system-wide tasks with associated rights. These system-wide tasks deal with the management and operation of Reporting Services as a whole. The system-wide tasks within Reporting Services are shown in Table 10–2.

Table 10–2: System-Wide Security Tasks within Reporting Services
Task	Description
Execute Report Definitions	Start execution of a report from a report definition without deploying it to the Report Server.
Generate events	Provide an application with the capability to generate events within the Report Server.
Manage jobs	View and cancel running Report Server jobs.
Manage Report Server properties	View and modify configuration properties for the Report Server.
Manage Report Server security	View and modify system-wide role assignments.
Manage roles	Create, view, modify, and delete role definitions.
Manage shared schedules	Create, view, modify, and delete shared schedules used for snapshots and subscriptions.
View Report Server properties	View properties that apply to the Report Server.
View shared schedules	View a shared schedule.

Again, you may not be familiar with all the tasks in this list. We discuss jobs and shared schedules in Chapter 11.

Roles

The rights to perform tasks are grouped together to create roles. Reporting Services includes several predefined roles to help you with security management. In addition, you can create your own custom roles, grouping together any combination of rights that you like. The predefined roles and their corresponding rights are listed here.

The Browser Role The Browser role is the basic role assigned to users who are going to view reports, but who are not going to create folders or upload new reports. The Browser role has rights to perform the following tasks:

Manage individual subscriptions
View folders
View models
View reports
View resources

The Publisher Role The Publisher role is assigned to users who are going to create folders and upload reports. The Publisher role does not have rights to change security settings or manage subscriptions and report history. The Publisher role has rights to perform the following tasks:

Create linked reports
Manage data sources
Manage folders
Manage models
Manage reports
Manage resources

The My Reports Role The My Reports role is designed to be used only with a special folder called the My Reports folder. Within this folder, the My Reports role gives the user rights to do everything except change security settings. The My Reports role has rights to perform the following tasks:

Create linked reports
Manage data sources
Manage folders
Manage individual subscriptions
Manage report history
Manage reports
Manage resources
View data source
View folders
View reports
View resources

The Content Manager Role The Content Manager role is assigned to users who are managing the folders, reports, and resources. All members of the Windows local administrators group on the computer hosting the Report Server are automatically members of the Content Manager role for all folders, reports, and resources. The Content Manager has rights to perform all tasks, excluding system-wide tasks.

The System User Role The system-wide security tasks have two predefined roles. The System User role has rights to perform the following system-wide tasks:

Execute Report Definitions
View report server properties
View shared schedules

The System Administrator Role The System Administrator role provides the user with rights to complete any of the tasks necessary to manage the Report Server. All members of the Windows local administrators group on the computer hosting the Report Server are automatically members of the System Administrator role. This role has rights to perform the following system-wide tasks:

Execute Report Definitions
Manage jobs
Manage report server properties
Manage report server security
Manage roles
Manage shared schedules

Creating Role Assignments

As stated previously, role assignments are created when a Windows user or a Windows group is assigned a role for a folder, a report, or a resource. Role assignments are created on the security page for the folder, report, or resource. These role assignments control what the user can see within a folder and what tasks the user can perform on the folder, report, or resource.

Let’s try creating role assignments for some of our folders and reports.

Note

To complete the next set of activities, you need a user who has rights to log on to the Report Server, but who is not a member of the local administrators group on that computer. You should know the password for this user, so you can log on as that user and view the results of your security settings.

Creating a Role Assignment for a Folder Let’s try creating a new role assignment for the Home folder:

Open the Report Manager in your browser. You should be viewing the contents of the Home folder.
Select the Properties tab. You see the security page for this folder.
Click New Role Assignment. The New Role Assignment page appears, as shown in Figure 10–17.

Figure 10–17: The New Role Assignment page
Type the name of a valid user for Group or User Name. If you are using a domain user or domain group, this must be in the format DomainName\UserName or DomainName\GroupName. If you are using a local user or local group, this must be in the format ComputerName\UserName or ComputerName\GroupName.
Check the check box for the Browser role.
Click OK to save your role assignment and return to the security page. Reporting Services checks to ensure you entered a valid user or group for the role assignment. If this is not a valid user or group, you receive an error message and your role assignment is not saved.

Note

A user needs to have at least viewing rights in the Home folder to view other folders and navigate to them.

Inherited Role Assignments By default, folders (other than the Home folder), reports, and resources inherit their role assignments from the folder that contains them. You can think of the nested folders as branches of a tree, with the reports and resources as the leaves. Inherited security means you can make security changes to one folder and have those changes take effect for all the branches and leaves further along the tree.

This makes managing security easy. You can maintain security for all the reports and resources within a folder simply by modifying the role assignments for the folder itself. You can maintain security for an entire branch of the tree structure by modifying the role assignments for the folder that forms the base of that branch. Let’s look at the security for the Galactic Delivery Services folder:

Select the Contents tab of the Home folder.
Select the Galactic Delivery Services folder to view its contents.
Select the Properties tab. You see the properties page for this folder.
Select Security from the left side of the page. You see the security page for this folder.

The Galactic Delivery Services folder is inheriting its role assignments from the Home folder. You did not add a role assignment giving Browser rights to your user in this folder and, yet, there it is! As soon as you added the role assignment to the Home folder, it appeared for all the items within the Home folder.

You gave your user Browser rights in the Home folder, so they could view the contents of the Home folder, and then navigate into other folders to find the reports they need. You may want to give this user additional rights in folders further along in the tree. Perhaps the user can manage the content of certain folders that belong to their department, but can only browse when in the Home folder.

To accomplish this task, you must first break the inherited security for the Galactic Delivery Services folder:

Click Edit Item Security. A dialog box with an inherited security message appears. The Report Manager is confirming you want to break that inheritance by creating your own role assignments for this folder.
Click OK to confirm you want to break the inherited security.

Now that you have broken the inherited security, you have new buttons on the toolbar for adding a new role assignment, deleting existing role assignments, and reverting to inherited security.

Now you can edit the role assignment for your user:

Click the Edit link next to the role assignment giving your user Browser rights. The Edit Role Assignment page appears.
Uncheck the check box for the Browser role.
Check the check box for the Content Manager role.
Click Apply to save the changes to your role assignment and return to the security page. The user now has Content Manager rights in the Galactic Delivery Services folder.
Click the Contents tab.
Select the Rendering Test Reports folder to view its content.
Select the Properties tab. You see the properties page for this folder.
Select Security from the left side of the page. You see the security page for this folder.

You can see the Rendering Test Reports folder is inheriting its role assignments from the Galactic Delivery Services folder.

Note

Although we do not do so in these exercises, you can check more than one role when creating or editing a role assignment. The user’s rights are then the sum of the rights granted by each role.

Managing Role Assignments for Reports Now, let’s try managing role assignments for reports:

Select the Contents tab.
Click Show Details.
Click the icon in the Edit column for the RenderingTest report. The properties page for this report appears.
Click Security on the left side of the page. The security page for this report appears.

Again, you can see this report is inheriting its role assignments from the folder that contains it—in this case, the Rendering Test Reports folder. Because the user has Content Manager rights for the folder, the user also has Content Manager rights for the report. This means the user can change any and all properties of this report and even delete the report altogether.

To continue our security example, we are going to suppose it is alright for the user to have Content Manager rights for the Rendering Test Reports folder, but not for the RenderingTest report. We need to edit the role assignment for your user. However, before we can do this, we must break the inheritance, as explained in the following steps.

Click Edit Item Security. The confirmation dialog box appears.
Click OK to confirm.
Click the Edit link next to the role assignment giving your user Content Manager rights. The Edit Role Assignment page appears.
Uncheck the check box for the Content Manager role.
Check the check box for the Browser role.
Click Apply to save the changes to your role assignment and return to the security page.
Click the Rendering Test Reports link at the top of the page.

Now we modify the rights granted to this user for the SubReportTest report. In our example, because this is a subreport, we assume the user should have limited rights to this report. In fact, they should only be able to review the report. In this case, the predefined Browser role has too many rights. We have to define our own custom role. To do so, follow these steps:

Click the icon in the Edit column for the SubReportTest report. The properties page for this report appears.
Click Security on the left side of the page. The security page for this report appears.
Click Edit Item Security. Click OK to confirm.
Click the Edit link next to the role assignment giving your user Content Manager rights. The Edit Role Assignment page appears.
Click New Role.
Type View Report for Name.
Type View Report Only for Description.
Check View Reports.
Click OK to save this new role and return to the Edit Role Assignment page.
Uncheck the check box for the Content Manager role.
Check the check box for the View Report role.
Click Apply to save the changes to your role assignment and return to the security page. The user has rights to view the SubReportTest report, but no other rights with that report.

We make one more change to test security. We remove all rights assigned to this user for the DrillthroughTest report:

Navigate to the Rendering Test Reports folder.
Click the icon in the Edit column for the DrillthroughTest report. The properties page for this report appears.
Click Security on the left side of the page. The security page for this report appears.
Click Edit Item Security. Click OK to confirm.
Check the check box next to the role assignment giving your user Content Manager rights.
Click Delete. The confirmation dialog box appears.
Click OK to confirm the deletion.

You can now close your browser, log out of Windows, and log on with the user name you have been using in the role assignments. Let’s test our security changes:

Open the Report Manager in your browser. You should be viewing the contents of the Home folder. Notice no buttons are in the Contents tab toolbar for creating folders and data sources or uploading files, as shown in Figure 10–18. That is because the user you are now logged on as has only Browser rights in this folder.

Figure 10–18: Browser rights in the Home folder
Select the Galactic Delivery Services folder to view its contents. When you are in this folder, the New Folder, New Data Source, Upload File, and Report Builder buttons have returned, as shown in Figure 10–19. In this folder, your user has Content Manager rights.

Figure 10–19: Content Manager rights in the Galactic Delivery Services folder
Select the Rendering Test Reports folder to view its contents.
Click Show Details.
Click the icon in the Edit column for the RenderingTest report. The properties page for this report appears. Note that Security doesn’t appear on the left side of the page, as shown in Figure 10–20. Your user has Browser rights to this report, so you can view the report and its history and create subscriptions, but you cannot change its security. (Don’t worry about what subscriptions are right now; we discuss them in Chapter 11.)

Figure 10–20: Browser rights for the RenderingTest report
Click the link for the Rendering Test Reports folder at the top of the page.
Click the icon in the Edit column for the SubReportTest report. The properties page for this report appears. Now, the Subscriptions tab is gone, as shown in Figure 10–21. Your user has the rights from our custom View Report role for this report. You can view the report and its history, but you cannot create subscriptions.

Figure 10–21: View Report rights for the SubReportTest report
Click the link for the Rendering Test Reports folder at the top of the page. Notice the DrillthroughTest report is nowhere to be seen because your user does not have any rights for this report, not even the rights to view it.
Click the RenderingTest report to execute it.
Go to Page 2 of the report. Scroll down to the table below the graph where you see Custer, Inc.
The heading Custer, Inc. is a link to the DrillthroughTest report. The problem is, your user does not have any rights to the DrillthroughTest report. Clicking this link results in an insufficient rights error message, as shown in Figure 10–22.

Figure 10–22: Insufficient rights error

Giving users only the rights they need is important. This prevents users from viewing data they should not see or from making modifications or deletions they should not be allowed to make. On the other hand, providing users with enough rights is important, so their reports function properly. We don’t want users to end up with an error message like the one shown in Figure 10–22 when they are trying to do legitimate work.

Role Assignments Using Windows Groups

As mentioned previously, role assignments can be made to Windows users or to Windows groups. If you create your role assignments using Windows users, you need to create a new set of role assignments every time a new user needs to access Reporting Services. This can be extremely tedious if you have a complex set of role assignments for various folders, reports, and resources.

In most cases, creating role assignments using Windows groups is better. Then, as new users come along, you simply need to add them to the Windows group that has the appropriate rights in Reporting Services. This is much easier!

Caution

In some cases, Internet Information Services (IIS) and, therefore, Reporting Services do not immediately recognize changes to group membership. This is because IIS caches some Windows security information, and then works from that cache. Stopping and starting the IIS service causes the IIS security cache to be reloaded with the latest and greatest group membership information.