ptg
381
Reporting Services Security Model
20
This is the same model used in NTFS. Every child item of a folder automatically inherits
the parent folder’s permissions. Whenever an item’s permissions need to change, just
break the inheritance and SSRS starts a new policy with that item.
Overview of Built-In Roles
For most organizations, the built-in roles should suffice. If they do not, keep in mind that
the Report Server administrators can create custom role definitions. If you need to create a
custom role definition, it might be helpful to stage that role definition in a development
environment.
Tables 20.2 and 20.3 describe the predefined roles and their corresponding tasks. Keep in
mind that when a task is called “Manage ...,” that it implies the ability to create, modify,
and delete.
TABLE 20.2
Item-Level Roles
Role Name Description
Browser Allows users to browse through the folder hierarchy, view report proper-
ties, view resources and their properties, view models and use them as a
data source, and finally, execute reports, but not manage reports. It is
important to note that this role gives Report Viewer the ability to
subscribe to reports using their own subscriptions.
Content Manager Allows users to manage folders, models, data sources, report history,
and resources regardless of who owns them. This role also allows users
to execute reports, create folder items, view and set properties of items,
and set security for report items.
Report Builder Allows users to build and edit reports using Report Builder and manage
individual subscriptions.
My Reports Allows users to build reports and store the reports in their own personal
folder. They can also change the permissions of their own My Reports

20.4 and 20.5 break down the system-level roles and tasks.
TABLE 20.5
Tasks Assigned to System-Level Roles
System Administrator System User
Execute report definitions X X
Generate events
Manage jobs X
TABLE 20.3
Continued
Browser Content
Manager
My Reports Publisher Report
Builder
Manage all subscriptions X
Manage data sources X X X
Manager folders X X X
Manage individual
subscriptions
XXX X
Manage models X X
Manage resources X X X
Set security for
individual items
X
View data sources X X
View folders X X X X
View models X X X
View reports X X X X
View resources X X X X
From the Library of STEPHEN EISEMAN

5. Enter
AdventureWorksSalesManagers
in the Group or User Name text box, and select
the Content Manager role, as shown in Figure 20.4.
6. Click OK.
To revert back to the parent security, click the Revert to Parent Security button, as shown
in Figure 20.5.
Manage Report Server properties X
Manage Report Server security X
Manage roles X
Manage shared schedules X
View Report Server properties X X
View shared schedules X X
TABLE 20.5
Continued
System Administrator System User
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
384
CHAPTER 20 Securing Report Server Items
FIGURE 20.1
Item security on the Properties tab.
FIGURE 20.2
Confirmation dialog box to break security inheritance.
FIGURE 20.3
New Role Assignment button.
To modify an item’s security, select a user or group by clicking the Edit check box next to
the assigned principal under Security (on the left). This returns you to the role assignment
screen, where roles can be added or removed.

5. Click OK.
To modify a role assignment, follow the steps to get to the appropriate property window.
From the property window, select Permissions and update the lists of tasks. To delete a role
assignment, select the role from the property window and click the Remove button.
Defining Custom Roles
SSRS allows administrators to create custom-defined roles to suit individual needs. This
can be a helpful feature for organizations that desire a finer degree of granularity, or if the
built-in roles simply do not suffice. Administrators can also modify any existing role.
Before jumping into creating new roles, a quick word of caution: It is very easy to get
carried away with creating custom roles. There might only be 25 tasks altogether (16
item level and 9 system level), but there are many different combinations you could
FIGURE 20.7
System Role Assignments screen.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
387
Defining Custom Roles
20
FIGURE 20.8
Adventure Works Sales Managers as System Users.
create. At this point, the management of roles might be just as cumbersome as managing
individual tasks.
Creating/Modifying a Custom Role
One of the roles SSRS lacks is a true “view-only” type of role. The following steps outline
how you could use Report Manager to create such a role. Later, you will use SQL Server
Management Studio to do the same thing.
The following steps create a new View Only Role using SQL Server Management Studio:
1. Open SQL Server Management Studio.
2. Click File and then Connect Object Explorer.

can either have a policy defined for it explicitly or will inherit the parent item’s policy.
If the built-in roles do not suffice, administrators are free to make their own.
FIGURE 20.9
Creating a custom role with SQL Server Management Studio.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
CHAPTER
21
Report Execution and
Processing
IN THIS CHAPTER
. Managing Schedules
. Report Execution and
Processing
. Report-Execution History
I
n this chapter, you explore some of the information that
can be captured at runtime, and learn how to set up shared
schedules that can be used to coordinate actions within the
Report Server.
Managing Schedules
Schedules are used within SSRS to trigger executions of
subscriptions and snapshots, generally classified as events.
Schedules can trigger a one-time event, or cause events to
run continuously at specified intervals (monthly, daily, or
hourly).
Schedules create events on the Report Server. Actions within
the Report Server, such as expiring a snapshot or processing
a subscription, are triggered by the event. What SSRS actu-

Have to be modified to change the
time
Manageability Are managed centrally from the Site
Settings tab in the Report Manager
or Object Browser
Have to be managed by the
individual items
Customizable Cannot be customized for a specific
item
Can be easily modified without
any other down-stream implica-
tions
are not taken. If the Report Server Service is down, the jobs show that they ran success-
fully, but no processing actually occurs.
Types of Schedules
There are two types of schedules used in SSRS: a shared schedule and a report-specific
schedule. The relationship is analogous to the relationship between a shared data source
and a custom data source. The shared schedule can be used to trigger a number of events
throughout the Report Server. A report-specific schedule is used for one and only one
specific event. A second event might occur at exactly the same time, but as far as SSRS is
concerned, it is a different schedule. Because they are so similar, the question often
brought up is “When should you use a report-specific schedule over a shared schedule?”
In general, create a report-specific schedule if a shared schedule does not provide the
frequency or recurrence pattern that you need.
Table 21.1 details the difference between shared schedules and report-specific schedules.
Creating/Modifying Schedules
The process of creating/modifying schedules is generally the same whether it is a shared or
report-specific schedule. The only difference is the scope. For the shared schedule, it is
created once and can be referenced in a subscription or property page when you need to
specify schedule information.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
392
CHAPTER 21 Report Execution and Processing
Deleting a schedule does not guarantee that the events that it triggers will stop firing.
Deleting a shared schedule serves only to create report-specific schedules for any items
that reference it. A better way to stop a schedule is to expire it, by putting an end date on
it. Expired schedules are indicated as such by the Status field. Schedules that have been
expired can be restarted by extending the end date.
Another alternative is to pause a shared schedule. A paused schedule can be resumed at a
later date and time. Report-specific schedules cannot be paused. Pausing a schedule is
similar to modifying it. Pausing the schedule of a process that is already running or of one
that is in queue only stops the subsequent runs. It has no effect on the currently execut-
ing process.
NOTE
Administrators can pause schedules from Report Manager.
To pause a shared schedule, select it from the list of the Report Manager schedules and
click the Pause button. The same process is used to delete a shared schedule.
Report Execution and Processing
Report processing is the process by which the instructions contained in the Report
Definition Language (RDL) file are used to gather data, produce the report layout, and
create the resulting report in the desired output format. Report processing is triggered by
either on-demand report processing or from push access. Depending on the settings, the
Report Server will perform either full processing where is goes through all the stages, or
will perform the last stage of report processing (rendering the final document from the
intermediate form).
The SSRS report processor generally executes reports in a three-stage process. Report
processing retrieves the RDL and gathers the data. With the layout information and the
base data, the report processor then does the following:
. Retrieves the report definition: This retrieval includes getting the report definition

Report-Execution Timeouts
The time it takes to process a report can vary tremendously. While reports process, they
take up time on the Report Server and possibly the report data source. As a matter of prac-
tice, most long-running reports take a long time to process because of a long-running
query.
SSRS uses timeouts to set an upper limit on how much time individual reports can take to
process. Two kinds of timeouts are used by SSRS: query timeouts and report-execution
timeouts.
Query timeouts specify how long an individual query can take to come back from the data
source. This value is specified inside the reports, by specifying the timeout property while
creating a data set. Query timeouts can also apply to data-driven subscriptions.
The report-execution timeout is the amount of time a report can take to process. This
value is specified at a system level, and can be overridden for individual reports. To set
this setting, click the Site Settings tab and modify the Report Execution Timeout property.
The default value is 1800 seconds.
SSRS evaluates the execution timeout for running jobs every 60 seconds. What this means
is that every minute, SSRS enumerates through every running job and compares how long
it has been running against how long it is supposed to run. The downside of this is that
reports actually have a bit more time than the specified timeout value in which to run. If
the timeout for a report is set to 30 seconds, SSRS does not check to see whether it
exceeded the timeout until 60 seconds, so the report actually gets an additional 30
seconds of runtime.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
394
CHAPTER 21 Report Execution and Processing
Running Processes
A process in the Report Server is also called a job. The two kinds of jobs are user jobs and
system jobs. User jobs are those jobs that are started by individual users or by a user’s

However, in the real world, you might run into a report that, when rendered, equals
hundreds of pages. For these reports, you need to take into account some special consider-
ations.
First, the amount of time a report takes to process is almost directly proportional to the
number of rows returned from the database query, and how long it takes to get those rows
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
395
Report-Execution History
21
back. It is a good idea to check with the DBA before running long-running queries against
a database. Also, check the execution plan of the query before running it. Perhaps further
indexing can be done. And finally, don’t bring back any more rows than needed. Modern
relational database management systems (RDBMS) are very good at sorting and grouping
data. Let the RDBMS group and sort the data where it can; this saves CPU cycles on the
Report Server and the network traffic.
Second, take into account the rendering. You should note that different rendering exten-
sions have different effects on the Report Server. The fastest extensions and those that use
the least amount of RAM are those whose output is essentially text (MHTML, CSV, and
XML). Excel and PDF are very resource intensive, whereas TIFF and JPEG fall in between
the two extremes.
Third, take into account the delivery method of the report. If a report uses pagination, it
can be rendered like any other report. The default rendering format is HTML, which
includes a soft page break. The page break is included intentionally and, in effect,
produces a sort of poor man’s paging. If a report is extremely large, this helps to deliver it
via browser. If the report is delivered via subscriptions, it makes sense to deploy it to a file
share and let the user’s desktop be responsible for opening it. This takes the load off the
Report Server and is the recommended course of action if using PDF or Excel.
The following list includes some general tips to help handle large reports:

. Amount of time the server took to process the report in milliseconds
. Type of data used for execution (live, cached, snapshot, history)
. Final status code of the report processing (success or first error code)
. Final size of the rendered report in bytes
. Number of rows returned in the data sets of the rendered reports
How to Report Off the Execution Log
The downside of the execution log is that it is not in a human-readable format. To remedy
this, Microsoft has distributed a SQL Server Integration Services package that can be used
to port the data from the Report Server’s internal execution log table to another database
to be used for querying and reporting against the log. There are even some sample reports
against the resulting execution log table. If you are still using SQL Server 2000, an equiva-
lent DTS package does the same thing.
Three files are central to the extraction and reporting of the execution log. All three files
should be located in the
<Program Files\Microsoft Sql Server\100\Samples\Reporting
Services\Report Samples\Server Management Sample Reports\Execution Log Sample
Reports>
directory. The first file is
Createtables.sql
, which is the script used to create
the tables for the RSExecutionLog database. The second two files,
RSExecutionLog_Update.dtsConfig
and
RSExecutionLog_Update.dtsx
, form the integra-
tion package that pushes the data from the Report Server catalog into the RSExecutionLog
database.
Creating the RSExecutionLog Database
You can create the RSExecutionLog database by completing the following steps (see Figure
21.2):

Creating RunLogs...
Script completed.
4. Double-click the DTSX file and click Execute to execute the package.
To keep data in the RSExecutionLog database current, periodically run the integration
package. The package is designed to import new data, without overwriting or removing
existing data. To remove old data in the RSExecutionLog database, run the
Cleanup.sql
script.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
398
CHAPTER 21 Report Execution and Processing
FIGURE 21.3
Report Execution Summary report.
Overview of the Sample Reports
Three reports come included with the sample reports packages.
The first report (
Execution Status Codes.rdl
) includes a summary of reports run by the
status they received. This shows the failure rate of reports on the server and why the
processes failed.
The second report (
Execution Summary.rdl
) gives an overview of report executions. It
includes some key metrics, such as the number of reports processed per day, the top ten
most requested reports, and the longest-running reports. This report is shown in Figure 21.3.
The last report (
Report Summary.rdl
) is similar to the execution summary, but gives the