Lesson 3: Enabling Row-Level Security in Management Studio
New: 14 April 2006
Enabling row-level security within a report model is a two step process: first you use Model Designer to create at least one filter attribute and assign it to the SecurityFilters collection for the entity for which you are applying row-level security, and then you use Management Studio to grant row-level permissions based on the filters that you created in Model Designer.
Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows.
In this lesson, you will start SQL Server Management Studio and apply model item security using the filtered attribute that you created in Lesson 2.
To start SQL Server Management Studio
Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
The Connect to Server dialog box appears.
In the Server type drop-down list, select Reporting Services.
In the Server name drop-down list, connect to the report server that stores the model.
Verify that Windows Authentication is selected and then click Connect.
In Object Explorer, the contents of the report server are displayed.
To grant access to the Adventure Works report model
In Object Explorer, double-click Home.
Double-click Models.
Right-click Adventure Works and then select Properties.
In the Select a page pane, select Model Item Security.
Select the Secure individual model items independently for this model check box.
Note
To apply model item security, this check box must be selected.
Select the Adventure Works top node and then click Add Group or User.
Type Everyone and then click OK.
Select the Model Item Browser check box.
The entire report model is now visible to everyone who has permissions to the report model.
To grant row-level access to the Adventure Works report model
Expand the Employee entity.
Select the UserIDFilter attribute.
Select the Use these roles for each group or user account option.
Select the Everyone group and then click Remove.
Click Add Group or User.
To grant access to Rachel0, type <ComputerName>\Rachel0 and then click OK.
Select the Model Item Browser check box.
Click Add Group or User.
To grant access to Garrett1, type <ComputerName>\Garrett1 and then click OK.
Select the Model Item Browser check box.
To close the Model Properties dialog box, click OK.
Next Steps
You have successfully applied row-level security on a model item in the report model. In the next lesson, you will validate these changes by building a simple report in Report Builder and then viewing the report as Rachel0 and as Garrett1 to verify that row-level security works as you expect. See Lesson 4: Verifying the Applied Row-Level Security Changes.
See Also
Other Resources
Securing Models
Model Properties (Model Item Security Page)
Managing Models