locked
Row-Level Security SSRS SQL Server 2008 RRS feed

  • Question

  • I am very new to SSRS 2008 and am struggling with some of the processes. 

    I am trying to develop a report in SSRS which will return only sales data for a specific sales rep. 

    My problem is that the query/view returns a slprsnid (salesperson ID) that does not directly relate to the UserID (Built in Field) (ie slprsnid = JC and UserID = <domain>/jcastle)

    What is the best way of managing this.  Please be warned that my SQL skills are also not advanced. 

    Any assistance would be appreciated.

    Judi

    Friday, August 12, 2011 5:40 AM

Answers

  • Hi JC-ASC,

    As KEROBIN mentioned, we can make use of User collection in Reporting Services.

    The User collection contains UserID and Language members in SQL Server Reporting Services. The UserID member can be used to enforce row-level security, such as to pass the user identity to the data source for restricted data shown on the report.

    1.       Add a LoginID column to the dataset from database.

    2.       Create a new filter for the dataset as
    LoginID = “User!UserID”

     More information about Using the User Collection, please see: http://msdn.microsoft.com/en-us/library/dd255216(v=SQL.110).aspx.

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.
    • Marked as answer by Lola Wang Monday, August 22, 2011 3:16 AM
    Monday, August 15, 2011 2:22 AM
  • Hi JC-ASC,

    It is quite right that the LoginID format must match with UserID as <Domain>\username.

    And based on my experience, the expressions in Reporting Services in usually case sentitive. Please be careful.

     

    Lola


    Please remember to mark the replies as answers if they help.
    • Marked as answer by Lola Wang Monday, August 22, 2011 3:16 AM
    Monday, August 15, 2011 7:14 AM

All replies

  • You can get the userid as parameter using the following expression.
    =User!UserId
    

    For further assistance, pls send us the query.

     

    Pls mark as answer, if this helps.

    - Kerobin
    Friday, August 12, 2011 7:16 AM
  • Hi JC-ASC,

    As KEROBIN mentioned, we can make use of User collection in Reporting Services.

    The User collection contains UserID and Language members in SQL Server Reporting Services. The UserID member can be used to enforce row-level security, such as to pass the user identity to the data source for restricted data shown on the report.

    1.       Add a LoginID column to the dataset from database.

    2.       Create a new filter for the dataset as
    LoginID = “User!UserID”

     More information about Using the User Collection, please see: http://msdn.microsoft.com/en-us/library/dd255216(v=SQL.110).aspx.

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.
    • Marked as answer by Lola Wang Monday, August 22, 2011 3:16 AM
    Monday, August 15, 2011 2:22 AM
  • Thank you to both.

    I have done as suggested and have added a column to the data view where a loginid is allocated based on the salesperson id in the view using:

    case when slsprnid = 'sb' then '<domain>\sbond' else '<domain>\jcastle' end as LoginID

    However I am getting no data being returned to the report based on the else part of this statement - as it is the only loginID that I can test.  The dataset returns values if run from the query builder.. but it doesn't filter them at that level. 

    If the placeholder for the UserID is used it prints on the report as <domain>/<domain user id> so I assumed that the LoginID needs to match that.  Is that in case the fact or is the domain name not required. 

    Additionally is this particular enquiry case sensitive?

    Or finally is there a problem with the case statement, especially when I am trying to allocate any other sales person id not trapped by the case statement to a particular login ID?

    Again thanks for your help.


    Monday, August 15, 2011 7:05 AM
  • Hi JC-ASC,

    It is quite right that the LoginID format must match with UserID as <Domain>\username.

    And based on my experience, the expressions in Reporting Services in usually case sentitive. Please be careful.

     

    Lola


    Please remember to mark the replies as answers if they help.
    • Marked as answer by Lola Wang Monday, August 22, 2011 3:16 AM
    Monday, August 15, 2011 7:14 AM