SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Securing Reporting Services Reports

By Mohamed Idttalbe,

This article is to exlain how to use Userid and ReportName from Reporting Service 2005. The goal of this article is to show an alert message to users when they are not allowed to see a report. I think this is more explicit than using Reporting Services security. We can also extend this method by filtering rows depending on the user.

First, you have to create a report server project within BIDS 2005. Add an element on this report, for example a table component. Then you need to create 2 variables :

  • User (String type, Internal)
  • ReportName (String type, Internal)

You will use these variables to store the user name and the current report name. The UserID global contains the domain name and the user name :

<domain name>\<user name>

To get the user name only, we will have to split the variable like this:

=CStr(Choose(2,Split(User!UserID,"\"))

This value will be stored in a report variable like this :

To get the report name, it is simple. We only need to store it to a variable like this :

In order to filter correctly the report, we will have to create a SQL table which contains the name of the user and the report associated. This table will be created in a user db :

CREATE TABLE UserRights(
User varchar(50),
Report varchar(50),
Visible bit)

Here is some sample data, with two users and a report.

UserRights
User Report Visible
User1 Report1 True
User2 Report1 False

The request to check the user right will be :

--Request that check user rights
SELECT count(*) as OK 
FROM UserRights 
WHERE User = @User and Report = @ReportName and Visible = True

We need to add a new dataset we will name TestReport that contains the request above. I suggest you create a stored procedure for that. For hiding the element, you need to edit the visibility property and add an expression :

The expression will look like this :

When the user is not allowed to see the report, you will have to add a textbox element and use the expression above on the visibility property. You just have to switch the True/False like this :

=IIf( Sum(Fields!OK, "TestReport", False, True)

Finally we need to display an error message to the user whom cannot see the report :

And choose an "expression" as a value :

You can easily hide report from non allowed users by using this technique. This method can be extended by adding column in the users table wich will be used to filter a query and hide non allowed lines.

 
Total article views: 7178 | Views in the last 30 days: 4
 
Related Articles
FORUM

SSRS Report visibility issues

Control the visibility of entire report based on a value

ARTICLE

Tutorial: Changing the Visibility Objects in Reporting Services

In the last tutorial in this series of Reporting Services tutorials we added conditional formatting ...

FORUM

How to Visible Group Header While Scrolling in Matrix Reports

How to Visible Group Header While Scrolling in Matrix Reports

SCRIPT

Report Progress with Variables

Script task in my package's OnProgress event handler reports all variable values at each step.

FORUM

Hidden itemm in report gets visible when report is exported to excel

Hidden itemm in report gets visible when report is exported to excel

Tags
 
Contribute