JohnAeris
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
Questions
Thanks!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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?
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.
ASKER
mcmahon_s: you're a star!
All sorted and working. Thanks!
All sorted and working. Thanks!
ASKER
I'm in the process of building the table. Turns out there are over 10,000 users! Will update once I've made progress.