How to control/filter data access using Windows server AD groups

• tomas.hertzman

  SSC Rookie

  Points: 35

  More actions

  March 12, 2010 at 2:09 am

  #212375

  Hi there, all.

  I'm new at this forum and I've run into a problem so I thought I give this forum a shot.

  My question seems to me to be quite "basic" in the meaning "very common problem" but I've searched on internet and plowed through some books but have not found any good answer.

  So here goes...

  The case is this:

  We're building a BI reporting environment using Windows Server 2008 and Sql server 2008.

  We are going to load data from about 70 Oracle databases into one staging area using SSIS. These databases is at different retailers who have one database each. From this stagingarea we're going to build a DW, using SSIS again, as a base for creating reports using SSRS.

  The users at the different retailers (about 1000 in total) should only to be able to view data belonging to their own store (using a StoreID). The exeption is (ofcourse) some managers at different levels who should be able to see data from a bunch of stores, and some of the managers all the stores.

  We have an intranet with AD configured and I would like to use the AD groups to control the access to the data to keep the user administration and access control in one place.

  Some larger "store groups" have their own AD but have a trust with our AD.

  My plan was to create one AD group per store to be able to add access rights to the data for each user in an easy way by just asigning different groups to different users and thereby minimizing the user administration in the database.

  What I don't know is how to "connect" the AD groups to permissions on the data in the database.

  So I guess my questions are these:

  1, Would this solution work?

  2, How do you filter the data for each user? Can you do it using some kind of Role/Schema in the database. I would rather not have 70 identical copies of the same database object (or database for that matter). Administrative nightmare. 🙂

  3, Should I try to filter the data using access control in the database or is it better to try to filter the data in SSRS? I'we read about the topic in the book "SQL Server 2008 Reporting Services Step by step" (Microsoft) but niehter the soultion of linked reports or a user table seems very appealing to me.

  4, Is it possible to use some kind of variable to get a users AD group? I know you get the userID by "User!UserID". Is there any way to read the users groups the same way?

  There ought to be a way to automate this, right?

  I don't expect a complete answer but maybe someone in here can point me in the right direction or give me some hints of "best practices". 🙂

  The SQL server database structure is not set so I can do practically whatever I want regarding that.

  Thanks in advance..

  Regards

  Tomas