Tutorial: Applying Security Filters to Report Model Items
In SQL Server 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 Report Manager.
To apply model item security, you must deploy the model to a report server and then, on the Security page in Report Manager, you can apply security on entities and fields within the model. 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 AdventureWorks2008R2 report model by using the SecurityFilters collection. Then you will apply the requisite security settings in Report Manager.
Important
SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.
Requirements
To use this tutorial, your system must have the following installed:
Microsoft SQL Server Reporting Services running in native mode. Support for models is not available in SQL Server Express. For more information, see Features Supported by the Editions of SQL Server 2008 R2 including:
Microsoft SQL Server.
Microsoft SQL Server Business Intelligence Development Studio.
Microsoft SQL Server with the AdventureWorks2008R2 database.
Microsoft SQL Server with the Adventure Works report model sample.
The Microsoft .NET Framework 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 AdventureWorks2008R2 database.
In addition, verify that the computer running the report server is using Internet Information Services (IIS) with integrated security.