August 7, 2009 at 11:53 am
Scenario:
Our Company has 6 locations.
Users from each location must only see data associated with their location, but some users should see data from 2 or more locations.
I created a SQL 2005 Database with a table for web report security with the following fields:
ReportName (char)
UserName (char)
Location (char)
DefaultLocation (bit)
[font="Courier New"]Table looks like this:
ReportName UserName Location DefaultLocation
MyReport MyDomain\Sam Washington True
MyReport MyDomain\Sam Oregon False
MyReport MyDomain\Frank Washington True
MyReport MyDomain\Jack Idaho True[/font]
Now back to the report.
Report Parameters:
Global/UserName and Global/ReportName (both hidden)
Location Dropdown using a dataset based on the table above, passing it the ReportName and UserName.
Main dataset for the report uses the selected Location parameter.
This works great. The NT Authenticated user only sees the location associated with their login/Report Name.
My questions to all of you Report Server Gurus is:
Is this the best way to handle security for BIDS Reports? What is the best practice?
Remember the report relies on the users location(s).
We are playing around with using Active Directory to handle the security (I know you can add AD users to the Reports Permissions and probably AD Groups to it as well), but that will not handle the Location criteria that the report needs. I do not want to create separate reports for each location and I do not believe that setting up hundreds of Groups in Active Directory is the best practice as we will have hundreds of reports.
Your suggestions are greatly appreciated.
-Kim
August 7, 2009 at 1:26 pm
I contracted as a report writer for a company that had similar requirements. They had different report folders for each client and used "linked" reports. The linked reports had default parameters set so that the report only showed data for that client. Then they used AD users/groups to limit access to the folders.
Not saying that's the best way, but it is one way.
I like what you are doing because it gives flexibility, but who manages the web report security table? Are you going to provide an interface to maintain that or you will you and/or other DBA's be responsible for adding/removing users from that table? Using the method I describe then your AD admins have the responsibility, especially if you use groups.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2009 at 2:18 pm
Jack Corbett (8/7/2009)
I contracted as a report writer for a company that had similar requirements. They had different report folders for each client and used "linked" reports. The linked reports had default parameters set so that the report only showed data for that client. Then they used AD users/groups to limit access to the folders.
Hadn't thought of that. So if I created a folder for all 6 locations and created AD Groups to contain the selected users for a particular location, can you administrate the folder permissions directly from Active Directory or does someone have to go to the folder itself and ad the AD Groups to the permissions there?
To answer your questions on the way I am currently doing it, yes I would make a web page that would let an administrator add, modify and delete the records.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply