I'd like to create a SSRS report which utilizes data from our Data Warehouse, DW. This DW has summarized, for example, the sales of 100 or so stores into one table FactStoreSale.
In the report I've managed to see which AD user who has started the report, by using the expression
=User!UserID as an default value in a parameter.
Each AD user is included in a AD Security Group. This Group typically includes the manager
Domain\first.lastname and the employees shared account
Domain\Store1000User. The name of the Security Group is typically
GRP_1000 (for StoreKey 1000).
Is it possible to allow users in a Security Group to only see data corresponding to that StoreKey, i.e. if some user included in the Security Group
GRP_1000 uses the report, the resulting set will be:
Select Sales, Quantity FROM FactStoreSale WHERE StoreKey = 1000
Note: I do not wish to create a report for each Store, as that would require 100 reports. Some managers can manage more than one store, so they may be included in multiple Security Groups and need the corresponding permissions.
I do not want to split up the underlying table
FactStoreSale, even though it should be possible to split this up into 100 or so schemas, and give members of the Security Groups access to these scehmas accordingly.
A solution I have in mind is to create a table using SSIS which gets updated every day with the appearence:
SecurityGroup StoreKey GRP_1000 1000 GRP_1001 1001 GRP_1002x 1002
and then use this table to reduce the amount of stores a user can see data from.
I do not know how I can utilize the Security Group in SSRS though, seeing as I have only found
UserID in the Built-in Fields in SSRS, but nothing about their respective Security Groups.
I am also not sure if this is the optimal way to go, as it requires additional tables and manually updating the table (because I do not wish to automate this procedure yet to have a higher Control over this).
I found a way to get the permission path, i.e. Group belonging to a user using SQL server and the Query:
EXEC xp_logininfo 'Domain\User.Name', 'all'
I've tested in SSRS that I can replace 'Domain\User.Name' with my
@User_id variable. I've tested to create a dataset with this query. This seems to give the correct data, but what it lacks is the StoreKey column which I thought could be
Cast(RIGHT(Permission_path,4) as int).
I'd like to be able to have a variable called
@StoreKey in SSRS where Available Values from Query would be something like:
Select Cast(RIGHT(Permission_path,4) as int) FROM EXEC xp_logininfo @User_id, 'all'
, but naturally I can't make an EXEC in the FROM clause. I also tried to make a new Dataset in SSRS which would use data from another Dataset within the same Project but that did not work either (and I Googled that it is not possible).
Edit: final solution
What I did to implement this was to create a table of the appearence:
ADGroup StoreKey Group_HQ 1010 Group_HQ 1011 Group_HQ 1012 Group_HQ 1000 Group_1010 1010 Group_1011 1011
I then created a variable called
StoreKey in SSRS where I used
Available values from dataset and where I had a dataset where I selected StoreKey using a JOIN on the current user's
AD Group using the Query provided by
sepupic and my table I had created. Thus I only got the StoreKeys available to certain AD Groups.