Link to home
Start Free TrialLog in
Avatar of JohnAeris
JohnAerisFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Build a User Access Table to Determine Access In SSRS Report

Hi Experts

Technologies: SQL Server 2008 R2 and SSRS Reports rendered using Report Manager

My current client is in the health industry.

We are building a drill-through report that contains sensitive patient data. Therefore we need to control access, the deeper you drill-through the 3 levels the more restrictive it becomes.

Considering there are hundreds of potential users across the county, this will add a huge administrative overhead.

To help share the load, the idea is to build a table that we can populate with every employee and determine their level of access. We can then build a simple front-end so that multiple managers can edit access levels for their respective teams.

Assumptions
We can access all employee data quickly.
Getting the table to interface with SSRS to help determine access levels is the crucial part right now. We can worry about the front-end later.

Questions
Any advice on how best to implement the above solution?
What are the potential challenges and stumbling blocks?
If you had the above challenge, what would you do to solve the problem?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of mcmahon_s
mcmahon_s
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JohnAeris

ASKER

Thanks mcmahon_s!

I'm in the process of building the table. Turns out there are over 10,000 users! Will update once I've made progress.
mcmahon_s. where would you put the query you wrote above? How would this stop access to a report?
It doesn't stop access to the report itself, it filters the rows they are able to see on the report. Based on your description above it would only allow them to see the patients they have permission for.
That's the plan! users will be checked against the team information that they can access, if they have permission they can view the rows of data.

So, where do I put the query? Does it live in the dataset?
Yes, you need to join the user table to your main query and add the where condition in the dataset for each report.
mcmahon_s: you're a star!

All sorted and working. Thanks!