SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Custom Code to Find Logged-In User's Global Groups?


SSRS Custom Code to Find Logged-In User's Global Groups?

Author
Message
Larry Schmidt-491187
Larry Schmidt-491187
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1347 Visits: 418
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
Larry Schmidt-491187
Larry Schmidt-491187
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1347 Visits: 418
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
Mohamed I.
Mohamed I.
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 348
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
Larry Schmidt-491187
Larry Schmidt-491187
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1347 Visits: 418
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
Mohamed I.
Mohamed I.
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 348
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search