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.
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.
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?