We have denormalised tables in our reporting database which provides data for creating ad-hoc reports in Report Builder 3.0.
What is the best way to model the data access in Report Builder?
The main criteria for accessing data for us determines by the centre user belongs to.
For example we have a table called Students, which is linked to other tables (e.g.: Enrollments). The Students table contains all student records from 8 different schools.
Each school has got a number of admin staff that should be able to see their own student details. They should are not allowed to see other schools students and the associated records (Enrollments, Courses, Letters, etc).
I'm wondering if I should include the 'School Name' in all tables, which can be used as a filter to only return the records related to that school for a given user? I feel hesitant to repeat this column in every single table. Is there any better way of handling data access level for this scenario?