0

Suppose i have 1000 rows in a dataset. The rows have an office/department column. What i would want is to create one report accessible by all users in the organisation but with a STRICT requirement that users should only view data for their office/department. i.e where office/department column value matches the office they belong to.

I'm thinking along the line of creating ssrs roles corresponding to the available offices & then matching the office/department column with the users role but there doesn't seem to be away of getting the current user's role name in SSRS. I'm only seeing User!UserID.

What would be the best reliable and easy to maintain way of handing this kind of requirement?

StackTrace
  • 9,190
  • 36
  • 114
  • 202
  • If the SQL Server version is 2016+ you might want to consider modifying the table itself to add Row Level Security. – Jacob H Mar 21 '19 at 15:27

1 Answers1

1

As you discovered, the User ID is all you have to work with in SSRS. You'll need to use that to lookup what their roles and permissions should be. So you'll need access to tables that have the correct mapping defined.

StevenWhite
  • 5,907
  • 3
  • 21
  • 46