Display data in Reports based on Active Directory Group Membership...

Hello,

I am fairly new to SQL 2005 and Reporting Services.

We are trying to create a report that will display sales data based on group membership from Active Directory.

For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.

Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.

Please let me know if anyone has a good way to tackle this.

Thanks,

Justin

Well, I'm not sure that this is the answer that you want to hear, but I would say that you would need to incorporate a "group" attribute in your SQL tables. How you implement that would be up to you, but I will tell you that bad database design can be quite chaotic.

If you are asking whether there is a magical function in SQL that will know how to interpret Active Directory membership without any extra design or programming, I would say definately no.

|||

What is the source of the data you want to limit by user account? SQL or Analysis Services.

If it's Analysis Services, you should be able to sort out the limitations by using Cube Roles connected to AD user groups.

See this article in SS2005 Books Online http://msdn2.microsoft.com/en-us/library/ms175408.aspx Granting User Access.

If it's from SQL Server 2005, it's not that simple. You can query Active Directory from SQL Server, so you can use some account data. Unfortunately, it's a bit of a hassle to create the right LDAP query. And you need to add some kind of group info to the database in any case, as you need to create a table containing the what data which group has access to, like GregSQL writes.

This article is on how to connect to AD from SQL Server

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

Kaisa Lervik

|||

I am working on this project with Justin - rather than limit access by the name of the group, we would like to pass the group name to the Analysis Services query in SSRS to determine which data is delivered to the logged in user.

Thanks

Bob