Tutorial: Applying Security Filters to Report Model Items
In SQL Server 2005 Reporting Services, model item security allows you to grant access to groups or users. Suppose your database contains sales order information. Unless you apply model item security, anyone with permissions to the model can view this sales order data. Model item security allows you to selectively expose items in the model to different users and groups. In general, this is similar to table and column security in a database. Model item security is enabled and configured using SQL Server Management Studio.
To apply model item security, you must first use Model Designer to create at least one filter or a default security filter. To use the filter as a security filter, assign it to the SecurityFilters collection. In SQL Server Management Studio, this security filter is then used to grant access to the specific model item that you want to secure.
You can also secure data returned by the model using row-level security. For example, you can allow sales people to see only their own sales orders. To apply row-level security, you need to create at least one attribute that you can use as a filter to restrict the data and then assign it to the SecurityFilters Collection property or the DefaultSecurityFilter Object property. This attribute must be a Boolean and the IsFilter property must be set to True. Optionally, if the attribute is not useful as a report field you can set the Hidden property to True.
As soon as you use at least one attribute for row-level security, by default all rows are hidden. Users gain access to rows based on security filters. Each attribute that you add to the SecurityFilters collection becomes a mechanism for granting access to the rows exposed by that filter. If security filters exist, users or groups who do not have permissions to any of these filters should not see any rows at all, unless a default security filter is defined. If one is defined, they will see only the rows exposed by the default security filter.
Note
Administrators do not have access to the entire model by default. If you want an administrator to have access to the entire model, then you must grant permissions just like any other user or group.
In this tutorial, you will learn how to apply row-level security to an entity within the Adventure Works report model by using the SecurityFilters collection. Then you will apply the requisite security settings in Management Studio.
Important
The sample databases are not installed automatically during setup, but you can install them at any time. For information about installing the samples, see Installing Samples.
Requirements
To use this tutorial, your system must have the following installed:
- Microsoft SQL Server 2005 Reporting Services running in native mode. Support for models is not available in SQL Server Express editions. For more information, see Features Supported by the Editions of SQL Server 2005, including:
- Microsoft SQL Server 2005 SP1 or later.
- Microsoft SQL Server 2005 Business Intelligence Development Studio.
- Microsoft SQL Server 2005 Management Studio.
- Microsoft SQL Server 2005 with the AdventureWorks database.
- Microsoft SQL Server 2005 with the Adventure Works report model sample.
- The Microsoft .NET Framework version 2.0 on the system that will run Report Builder.
You must have the following permissions:
- To deploy and publish the report model, you need to be assigned to the Content Manager or Publisher role.
- Permissions to retrieve data from the AdventureWorks database.
In addition, verify that the computer running report server is using Internet Information Services (IIS) with integrated security.
Tasks
Lesson 1: Setting Up Permissions for this Tutorial
Lesson 2: Creating Attributes for Row-Level Security
Lesson 3: Enabling Row-Level Security in Management Studio
Lesson 4: Verifying the Applied Row-Level Security Changes
See Also
Concepts
Other Resources
Installing Samples
SQL Server Reporting Services Samples
Sample Databases