SQL Server Reporting Services, how to best apply filters

Question

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.

Answer 1

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...

SELECT
   MY STuff
FROM
   Order O
   JOIN
   UserDept UD ON O.UserCode = UD.UserCode
WHERE
   EXISTS (SELECT *
       FROM
           UserDept UD2
       WHERE
           UD2.UserCode = @MYUSerCode
           AND
           UD2.DeptID = UD.DeptID)

--or
SELECT
   MY STuff
FROM
   Order O
   JOIN
   UserDept D ON O.UserCode = D.UserCode
   JOIN
   UserDept U ON D.DeptID = U.DeptID
WHERE
   U.UserCode = @MYUSerCode

Answer 2

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.

Answer 3

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)
)
RETURNS 
    @TempPermissions TABLE 
(
    DepartmentId Int
)
AS
BEGIN

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

    RETURN 
END

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

Pete