In the previous
installment of our series dedicated to the most prominent features of SQL
Server 2005 Express Edition, we continued our discussion of its implementation
of Reporting Services, focusing in particular on access control. As we have
demonstrated, it is possible to delegate specific system-wide and item-specific
tasks to designated users or groups by employing a built-in mechanism based on
a set of pre-defined roles. However, there are situations where this model does
not provide a sufficient level of flexibility. In this article, we will present
alternative methods that help address this shortcoming.
As we have mentioned earlier,
Reporting Services facilitates the customization of published reports without
modifying their original definitions (.rdl
files) by creating linked reports. Restricting the range of published data can
be accomplished by properly adjusting their security and parameters (displayed
in the Parameters section on
the Properties tab). Note
that despite these modifications, linked reports still point to the same data
source (although this can be altered as well, if desired), so, in essence,
their functionality is similar to what is provided by database views.
In general, in order to present only a portion of a full dataset to a target
user or group, you could create a linked report (referencing the original one,
providing a full view of the data), store it into a folder whose access
permissions are restricted to browsing, and configure its parameters to ensure
that only records meeting specific criteria will be displayed. Obviously, this
approach is predicated on the assumption that a set of parameters exist that
allow you to filter out data that is supposed to remain hidden. Let’s walk
through an example that illustrates such an approach.
We will use one of the sample
server management reports called Schema. In its original form,
Table Extended Properties for this purpose
it allows you to choose a target database (from the DatabaseName listbox) for
which you want to display schemas and columns. After the selection is made, you
can explore each schema, their tables and columns (down to the property level)
in the drilldown fashion. Let’s assume that we want to allow a specific user
(for the sake of example, we will refer to it as AWBrowser) to view definitions of only those objects that
reside in the AdventureWorks
database.
To start, create a folder within the site hierarchy, which will serve as a
container for our new linked report (we’ll call it Linked Reports, but obviously the naming
is entirely arbitrary). To accomplish this, launch an Internet Explorer while
logged on as an account with Content and
ManagerSystem roles. Once you reach the
AdministratorHome page of SQL Server Reporting Services site,
click on the New Folder
button in the toolbar, type its name in the Name:
textbox, and finalize your entry with the OK
button. After the folder appears in the site hierarchy, use its Edit icon to display its properties and
switch to the Security
section. Click the Edit Item Security
button in the toolbar to create a new role assignment, granting Browser
privileges to our designated account (AWBrowser).
With appropriate permissions in place, navigate to the Server Management Sample Reports folder
and locate the General
section within the Properties
tab of the Schema Table Extended report. Assuming you have sufficient privileges, you
Properties
should find the Create Linked Report
button there. After clicking on it, provide the new linked report name (let’s
call it AdventureWorks Schema Table) and use the
Extended PropertiesChange Location button to point to the Linked Reports folder. Once you confirm
your entries by clicking on the OK
button, you will be redirected to the target location automatically. Switch to
the Parameters section of
the Properties tab and make
sure that the Has Default
checkbox is selected, the Default Value
textbox contains AdventureWorks
entry, as well as that Prompt User
is unchecked. Click on the Apply
button. Verify that AWBrowser
is assigned the Browser role by reviewing the Security section. At this point, the new, more
restrictive, linked report is ready to be viewed by our designated user.
While the solution described above is fairly straightforward, it introduces
administrative overhead in situations where access to parameterized reports
needs to be granted to a larger number of users (and might make your Reporting
Services site cluttered and tough to navigate). In addition, it has a
dependency on the report’s parameters, which limits its flexibility considerably.
These drawbacks are eliminated when using an alternative approach, which
involves modifying the underlying data source and leveraging the built-in
capability to control the output of a report based on the name of a user that
generates it. More specifically, you can create an additional table providing a
mapping between each username and a field (column) that will constitute the
basis for filtering data that these users are supposed to be able to view. Such
a table is referenced by a slightly modified query on which the report is
based, which includes the WHERE
clause matching the value of the username column and the parameter representing
a current user. Finally, you have to alter this newly created dataset parameter
to indicate that its value will be populated automatically by the operating
system and (for this very reason) remove it from the list of explicit
parameters that the report will expect its users to provide.
Let’s demonstrate this method by using a report, whose creation we have
presented in our article The
Basic Functionality of Report Designer, leveraging a query included in the Sales and
Marketing Scenario described on the MSDN site. Its output, based on the
content of the AdventureWorks
database, listed all of the individual customers (including their full name and
address), grouped by State/Province and Country/Region. While in its original
form, the resulting report displayed all data; our goal will be to filter it,
such that its outcome will be geared towards individual regional managers,
(i.e. it will include only records of their customers).
Start by creating an auxiliary table, which will contain the mapping between
Reporting Services users and the country/region field stored in the database.
To accomplish this, launch SQL Server Management Studio Express, log on with an
account that has the db_owner
role in the AdventureWorks
database, and click on the New Query
toolbar button. In the new window, type in the following statement (we leverage
one of user-defined data types called dbo.Name
here, already referenced in Person.CountryRegion
table), and execute it:
USE AdventureWorks
GO
CREATE TABLE dbo.UserMapping
WindowsUser nvarchar(50) NOT NULL,
CountryRegion dbo.Name NOT NULL
)
This will result in the creation of our mapping table. In order to illustrate
its purpose, add a record consisting of a single pair of values to it (in our
case, this will be ColinHay
and Australia) designating
the name of our manager from the "land down under" and the region he
is responsible for.
Now open our sample
Reporting Services project in Business Intelligence Development Studio. In
the Designer window, switch to the Data
tab, invoke the Add Table...
command from its context sensitive menu, and select the newly created dbo.UserMapping. Once its representation
appears on the Designer’s surface, create a link between its CountryRegion field and its counterpart
(i.e. Name) in the CountryRegion (Person) table. Note that
this action will automatically update the T-SQL statement displayed at the
bottom of the window (by adding INNER JOIN).
dbo.UserMapping ON Person.CountryRegion.Name = UserMapping.CountryRegion
Next, append a condition, which will filter the result set to include only
those records where the value of UserMapping.WindowsUser
matches the name of a user who generates the report. This is accomplished by
adding the WHERE dbo.UserMapping.WindowsUser clause (note that
= @UserID@UserID
simply designates a parameter, which subsequently will be assigned a value
provided by the operating system). Still on the Data tab, click on the ellipsis
(...) button next to the Dataset listbox to launch its dialog
box, switch to the Parameters tab, and modify the entry containing the @UserID Name by setting its value to =User!UserID. (This value represents the
name of a Windows user in which the security context the report is executed).
In order to prevent having this parameter listed as available for assignment
once the report is published, remove it via the ReportReport Parameters... menu. At this point, you are
ready to deploy it (using the Deploy
option in the context sensitive menu of the report appearing in the Solution Expolorer window). Once our
regional manager logs on and executes the newly published report, its results
should include only records representing individual customers located in Australia.
In the next installment of our series covering various aspects of SQL Server
2005 Express Edition, we will look into other methods of securing Reporting
Services installation.
