How to Setup Report Parameters to Default Based On User Credentials

  • Jody Claggett-376930

    Ten Centuries

    Points: 1152

    Comments posted to this topic are about the item How to Setup Report Parameters to Default Based On User Credentials

    _______________________________
    [font="Tahoma"]Jody Claggett
    SQL Server Reporting Analyst
    [/font][/size]

  • SQLWorks

    SSC-Addicted

    Points: 444

    Is anyone capturing credentials from active directory without the need for a users table?



    SQL Tips and Scripts
    SQLWorks Blog

  • heb1014

    Hall of Fame

    Points: 3769

    I personally don't like the approach of capturing user credentials at the report level. It is better to do this from the database inside of a stored procedure so that there is only 1 method for accessing the data. You could still create a table that would map certain users to certain regions. Then inside the proc you would do something like this:

    IF SYSTEM_USER IN (SELECT UserName FROM dbo.ReportUsers)

    BEGIN

    SELECT DISTINCT

    SalesRegion

    FROM datatable

    WHERE

    (SalesRegion IN

    (SELECT SalesRegion

    FROM ReportUsers

    WHERE (UserName IN (@parUserID))))

    END

    ELSE

    SELECT DISTINCT

    SalesRegion

    FROM datatable

    WHERE SalesRegion IN ('Central', 'West', 'East')

    Also, using procs will allow you to not give direct access to tables and procs generally result in better performance. Performance may not an issue in this case since the query will probably be parameterized, but it is a good practice to use procs over ad-hoc sql.

  • mtillman-921105

    SSCertifiable

    Points: 7049

    Thank you for the aricle! You've clearly explained the methods and illustrated it well. I also think that this will be helpful sometime soon.

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • fabauer

    SSC Rookie

    Points: 31

    Is there any addon / 3rd Party Tool available to integrate this Feature of Parameter-Level permissions into the Report Manager? Because this solution looks a bit too static for me.

  • deanroush

    SSC-Addicted

    Points: 474

    We use table-driven custom roles in our budget reporting system and other systems. We store the active directory user login name as a custom field in our primary employee table (we are not yet fully integrated to Active Directory). We load parameter list values from a call to a stored procedure that first converts the domain login name (CURRENT_USER) to an employee ID, then does a lookup on that employee ID in the custom role tables to find which account numbers that employee's role is permitted to access. We return those account numbers out of the stored procedure which is then used to load report parameter 'Accounts'. No 'extra' tables are required.

    We use this same model in all report scenarios where custom filtering based on user is required.

  • vishalmehta78

    SSC Veteran

    Points: 293

    actually instead of storing logins in a table I have queried active directory & based on the credentials stored in it, the parameters could be driven. Will be happy to share my code if anyone is interested.

  • heb1014

    Hall of Fame

    Points: 3769

    Vishal,

    Please go ahead and post your code.

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the article, it was well written and expands my knowledge of Reporting Services.

  • UMG Developer

    SSChampion

    Points: 13482

    Vishal Mehta (4/27/2011)


    actually instead of storing logins in a table I have queried active directory & based on the credentials stored in it, the parameters could be driven. Will be happy to share my code if anyone is interested.

    Yes please, if you have any code for accessing AD from RS or SQL server I am sure there are many people that would be interested in seeing it.

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    Thanks for the article, I learned something new.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Peter Verstappen

    SSC Enthusiast

    Points: 128

    Dear Jody Claggett,

    I use your script “How to Setup Report Parameters to Default Based On User Credentials” in a hospital

    I changed the SalesRegion column in Specialisme to autrize hospital specialist to query result of there specialisme.

    I made a second parameter "parSpecialisme that i want to fill with the valuse from the table `datatable ` column `Specialisme´

    Now is my question: Is is possible to put in de SalesRegion (Specialisme) more than one item, so that it select in de SSRS report Multible salesRegion (Specialisme). I want to hide the parameter

    I seperated the values in the records by Comma {,} en by d0t-comma (;) but it don’t work.

    Thanks.

    Peter

  • Peter Verstappen

    SSC Enthusiast

    Points: 128

    Dear Jody Claggett,

    I use your script “How to Setup Report Parameters to Default Based On User Credentials” in a hospital

    I changed the SalesRegion Column in Specialisme to autrize hospital specialist to query result of there specialisme.

    Now is mine question: Is is possible to put in de SalesRegion (Specialisme) more than one item, so that you can select in de SSRS report Multible salesRegion (Specialisme).

    I seperated the values in the records by Comma {,} en by d0t-comma (;) but it don’t work.

    Thanks.

    Peter

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply