Take the 2-minute tour ×

I have a bunch of records (orders) that I want to make available to users to make reports from.

The users come from different departments, and I would like to make it, so each department can only see their own stuff.

I can't figure out how to do this the right way.

What I have now is: - A model where I have placed a Filter on the Order table.

The filter can use GetUserID() to get the users name, but I can't figure out how I get from that to the "UserDepartment" table that maps users to specific departments.

Ofcourse, I would prefer a solution whereby I didn't have to create new access groups or edit the model for each department that someone might dream up.

Any clues?

(Using SQL server 2008)

EDIT: This link http://blogs.msdn.com/bobmeyers/articles/Implementing%5FData%5FSecurity%5Fin%5Fa%5FReport%5FModel.aspx shows the basics of what I'm trying to do, but the author seems to assume that each record have a UserName field that can be matched.

In my case i want all users of department X to be able to access the line.

share|improve this question
What is the link between user/userdepartment and Order? –  gbn Aug 25 '09 at 17:13
Each order has a DepartmentId to the department owning the order –  Soraz Aug 25 '09 at 17:45
add comment

3 Answers

We had a similar problem to this and ended up writing a function in SQL.

The function did the following:

  1. Received the username parameter from SRSS
  2. Performed a lookup on the permissions table and retrieved the records (department Id's in your case).
  3. returned the department Id's

Then our sql statement looked like this:

SELECT * FROM ImportantData WHERE DepartmentId IN (SELECT Id FROM fn_GetUserDepartmentAllocations(@UserName))

This did force us to modify all of the sql queries but it allowed us to do it with minimal complex logic.

The other thing that this allows for is if you have one user who transcends department boundaries: for example a manager of 2 departments.

CREATE FUNCTION [dbo].[fn_GetUserDepartmentAllocations]
    @UserName NVARCHAR(100)
    @TempPermissions TABLE 
    DepartmentId Int

    	INSERT INTO @TempPermissions	
    	SELECT DepartmentId 
    	FROM DepartmentPermissions
    	WHERE DepartmentAllowedUsername = @UserName


The main benefit to doing it this way is it also allows you to edit one place to change the entire permissions structure, you don't have to go through each and every report to change it, instead you change one place

For example you could have a manager who belongs to 2 departments but is not allowed to view them except on thursdays (I know silly example but you get the point hopefully).

Hope this helps


share|improve this answer
add comment

This assume that Users have Orders. So, filter by users who exist in the same dept as the filter user. Don't filter orders directly.

I've guessed at schema and column names: hoep you get the idea...

   MY STuff
   Order O
   UserDept UD ON O.UserCode = UD.UserCode
           UserDept UD2
           UD2.UserCode = @MYUSerCode
           UD2.DeptID = UD.DeptID)

   MY STuff
   Order O
   UserDept D ON O.UserCode = D.UserCode
   UserDept U ON D.DeptID = U.DeptID
   U.UserCode = @MYUSerCode
share|improve this answer
When you create a Model for SRSS you can add filters and fields to the model, and that in turn creates its own SQL behind the scenes. Is this the same thing ? I fear that if I use SQL to define the datasource, I will not be able to use the GetUserID() of the model. –  Soraz Aug 25 '09 at 17:48
Sorry, I've not used models in SSRS. Surely this is data function anyway: a set based join/filter, and not soemthing to do in the report itself? –  gbn Aug 25 '09 at 18:02
@Soraz: You can still pass user id as a parameter to the data source right? –  Faiz Sep 9 '09 at 6:47
Not sure really. When you say "Create Model" in Visual Studio, it pretty much does everything on its own. I'm not really sure where I would implement any of these sql based approaches. –  Soraz Sep 14 '09 at 11:54
add comment

What you're trying to achieve is difficult using the GetUserID() method. To use that your source query would have to return a lot of redundant data, imagine something like the following:

Table: User
Fields: UserID, LoginName, FullName

Table: Department
Fields: DepartmentID, Name

Table: UserDepartments
Fields: UserID, DepartmentID

Table: Order
Fields: OrderNumber, DepartmentID

SELECT O.OrderNumber, O.DepartmentID, U.LoginName
FROM Order O
JOIN Department D ON D.DepartmentID = O.DepartmentID
JOIN UserDepartments UD ON UD.DepartmentID = D.DepartmentID
JOIN User U ON U.UserID = UD.UserID

This will give you more rows than you want, basically a copy of the order for each user in the department that owns the order.

Now you can apply your filter as described in the link you provided. This will filter it down to just one copy of the order rows for the current user if they're in the right department.

If this is a performance issue there's other alternatives, easiest being using a local report (.RDLC) in either ASP.NET, WinForms or WPF and passing user details off to the data call so the filtering can be done in the SQL.

share|improve this answer
add comment

Your Answer


