locked
Show different data for same report for different users. Is it possible? RRS feed

  • Question

  • Hi,

    I have a report which contains a column called "Project" in the dataset. There are several different projects and not every user should be able to see data from all projects. For example if I want to realize this scenario:

    User                 Project
    ----------------------------
    A                      1, 5, 7
    B                      4, 6, 9
    C                      11, 12

    How can I achieve this behaviour? I don't want to dublicate the report several times. Is there an easier way?

    Best regards
    pgosch

    Monday, September 23, 2019 7:51 AM

Answers

  • Hi,

    I have a report which contains a column called "Project" in the dataset. There are several different projects and not every user should be able to see data from all projects. For example if I want to realize this scenario:

    User                 Project
    ----------------------------
    A                      1, 5, 7
    B                      4, 6, 9
    C                      11, 12

    How can I achieve this behaviour? I don't want to dublicate the report several times. Is there an easier way?

    Best regards
    pgosch

    You can map to internal user ids to usernames created in the domain

    Then in your SSRS report you can create a parameter for username and use User!UserID to set its value

    Use the username and get the corresponding ID value from your mapping table (you would have to create one for mapping IDs against usernames)

    Then lookup for ID in above table to get project information

    Pass this as a filter for your backend query (or stored procedure) for getting the related data for your report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by pgosch Thursday, November 28, 2019 7:37 AM
    Tuesday, September 24, 2019 6:18 AM

All replies

  • Hi pgosch,

    This could be achieved by using Row Level Security in SSRS.

    There is a tutorial you can follow step by step, it contains codes and a video, check if it helps.

    Please refer to Implementing “Row Level Security” (RLS) with “Filter Predicate” in SQL Server 2016.

    Regards,

    Zoe



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Zoe Gu Wednesday, September 25, 2019 2:33 AM
    Tuesday, September 24, 2019 2:52 AM
  • Create a UserProjects table (and optionally a "Users" table too). Making some assumptions about your existing schema:

    create table UserProjects(userName varchar(...), Project int foreign key references Projects, constraint PK_userproject primary key (userName, Project))
    


    Populate this table and join to it when querying. You can use the original_login() function if you can detect the user from the transport layer (ie, each user is connecting through to SQL with their own credentials):

    select

    from Projects p join UserProjects up on up.project = p.project where up.userName = original_login()




    http://www.sqlservercentral.com/blogs/don_halloran/

    Tuesday, September 24, 2019 6:10 AM
  • Hi,

    I have a report which contains a column called "Project" in the dataset. There are several different projects and not every user should be able to see data from all projects. For example if I want to realize this scenario:

    User                 Project
    ----------------------------
    A                      1, 5, 7
    B                      4, 6, 9
    C                      11, 12

    How can I achieve this behaviour? I don't want to dublicate the report several times. Is there an easier way?

    Best regards
    pgosch

    You can map to internal user ids to usernames created in the domain

    Then in your SSRS report you can create a parameter for username and use User!UserID to set its value

    Use the username and get the corresponding ID value from your mapping table (you would have to create one for mapping IDs against usernames)

    Then lookup for ID in above table to get project information

    Pass this as a filter for your backend query (or stored procedure) for getting the related data for your report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by pgosch Thursday, November 28, 2019 7:37 AM
    Tuesday, September 24, 2019 6:18 AM