Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Custom Code to Find Logged-In User's Global Groups? Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 4:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 7:24 AM
Points: 217, Visits: 244
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
Post #1444186
Posted Thursday, April 25, 2013 7:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 7:24 AM
Points: 217, Visits: 244
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
Post #1446489
Posted Thursday, May 02, 2013 1:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 08, 2013 3:19 PM
Points: 34, Visits: 157
Hi,

I wrote an article discussing about that
Maybe it can help you ?

http://www.sqlservercentral.com/articles/T-SQL/66218/

Mohamed
Post #1448968
Posted Thursday, May 02, 2013 2:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 7:24 AM
Points: 217, Visits: 244
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
Post #1448974
Posted Friday, May 03, 2013 10:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 08, 2013 3:19 PM
Points: 34, Visits: 157
Ok i understand
Why did not you used a table populating the user and the associated group ?
So the filtering can be done easily

Post #1449286
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse