April 18, 2013 at 4:25 pm
It seemed like an eminently reasonable request, and something a Microsoft developer surely would have thought of, right?
After all, there is a Built-In Field that gives us UserID. OK, so what Windows AD groups is that user a member of, because that just might determine what information s/he sees in a report? A logical thing to expect . . .
We have an internal enterprise framework that provides some nice and easy (and reusable!) functionality to return Active Directory information, so perhaps I can just add a Reference in my SSRS Customer Code and call one of those handy functions. Oops - SSRS, even in Visual Studio 2010, supports only the .NET 2 Framework, so it doesn't like a reference to something newer.
OK, this can't be that hard, right? Using a couple of .NET (2!) references to System.DirectoryServices and System.Security, I wrote some code that should return all a user's groups. No more errors, but still doesn't seem to work.
Has anyone attempted something like this? It seems like something that might be commonly used, particularly for reports in a larger organization, where you might want to filter data by a user's security groups.
Any and all comments are greatly appreciated - thanks!
(To close the loop here, I should add that a separate table will have a list of which user groups can access which information, perhaps by a business segment code or similar.)
Larry Schmidt
United Health Group, UHG IT Infrastructure Services
April 25, 2013 at 7:28 am
It would seem that SSRS is the poor stepchild in the Microsoft SQL Server hierarchy! The "warning" I was receiving, even after coding a .NET 2 Framework process to obtain User Groups is essentially saying that SSRS does not have permissions to do Active Directory queries. Some Googling quickly showed that to be the case, including statements from Microsoft staff.
Seems odd, since any user can do a NET USER /DOMAIN userid at the Command Prompt to get that information. Our company's intranet even features an "NTLookup" tool for anyone to use.
At any rate, I have designed two SSIS processes to address this whole issue. One reads our current SSRS report inventory from a list on SharePoint. (Another SSRS stumbling block, by the way - the SSRS catalog is kept in the master db of the report server, a place that is generally verboten to anyone other than DBAs in most large organizations!) Another table links reports to a table containing User Groups that can view the report and what the data filtering criteria is.
The second SSIS process does a daily rebuild of a table that lists all user IDs for members of the groups found in the report/group cross-reference table.
A prototype report was designed in which the logged-in user ID is used to find out what user groups the user might be in for this particular report, and ultimately what filter to put on the data.
A rather roundabout way of achieving what could be remedied by SSRS improvements! If UserID is a built in field, why not the User Groups as well?
Larry
United Health Group, UHG IT Infrastructure Services
May 2, 2013 at 1:49 pm
Hi,
I wrote an article discussing about that
Maybe it can help you ?
http://www.sqlservercentral.com/articles/T-SQL/66218/
Mohamed
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
May 2, 2013 at 2:14 pm
Thanks for your reply, but your article really only addresses a very small part of the problem.
The requirement is to be able to check Active Directory to find out what user groups the logged in user is a member of, and then to filter the data according to rules set up for that group.
I have designed a complete solution that also requires two SSIS packages and have drafted a preliminary document outlining the entire process. I hope to submit this to SQLServerCentral in the near future, since I think it may break some new ground in what I would imagine might be a somewhat common reporting requirement.
Larry
May 3, 2013 at 10:15 am
Ok i understand
Why did not you used a table populating the user and the associated group ?
So the filtering can be done easily
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply