1

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

EDIT

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.

  • So your question is not about SSRS but about how to find AD groups to which given AD account belongs to? – sepupic Jan 10 '18 at 10:03
  • @sepupic My question is how I can present only a subset of the data in a table in SSRS, and one method I've thought of which may be applicable is to create a table over the AD Groups and which StoreKey they should be able to see data of. This solution is something I don't know how to implement in SSRS seeing as I can't find any Connection between UserIDs and Security Groups in SSRS. But as said, that is merely thought I've had and that perhaps theres a different way to achieve this in SSRS. – Cenderze Jan 10 '18 at 11:08
  • SSRS has no idea about Windows AD groups, so if you want to use the approach you've described your problem is what I've said, how to find AD groups to which given AD account belongs to – sepupic Jan 10 '18 at 11:18
  • @sepupic If SSRS lacks a way to determine Security Groups in AD from users this becomes a little more tricky. I suppose I can make a table as I presented earlier, but instead of "SecurityGroup" I have "ADName" with the names of each person and what Store it should have permission to see data from. This does seem very prone to error, and manual Labour. Do you have another idea on how this can be achieved? – Cenderze Jan 10 '18 at 12:41
  • Do you use one SQL Server login for everyone to access the report or do you use Windows logins? I mean what type of authentication is configured to access the report – sepupic Jan 10 '18 at 13:08
2

You can use this code to obtain all the Windows groups of which current login is a member:

select distinct name
from sys.login_token
where type = 'WINDOWS GROUP';

You can insert all these groups into a table variable/temp table and then use it in a join with your table that maps Win Groups to StoreKeys which you further join with the table of interest

  • Thank you! I did the following in order to test an implementation of this solution: select distinct * FROM(Select distinct Name from sys.login_token where type = 'WINDOWS GROUP') a JOIN (Select Name, StoreKey FROM (VALUES('DOMAIN\GRP_1204','1204')) b (Name,StoreKey) ) b ON a.name = b.name and it seemed to work! – Cenderze Jan 10 '18 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.