Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 7095 | Views in the last 30 days: 14
 
Related Articles
FORUM

SSIS Variable Expressions

SSIS Variable Expressions

BLOG

Monday Morning Mistakes: SSIS Expressions Not Evaluating Correctly

SSIS Expressions Expressions in SSIS are great. They allow you to create dynamic values for all...

FORUM

Expressions for Variables

Expressions

FORUM

Policy Based Management (PBM) - Conditions (Expressions: Regular Expressions)

SQL 2008 PBM allows Expressions when creating Conditions that allows the use of regular expressions

FORUM

Expression

Expression

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones