How to Setup Report Parameters to Default Based On User Credentials

Question

Post reply

How to Setup Report Parameters to Default Based On User Credentials

Jody Claggett-376930

Ten Centuries

Points: 1152
More actions
April 24, 2011 at 10:57 am

#237040

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]

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

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

SQLWorks SSC-Addicted Points: 444 More actions · Answer 1

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: 3790 More actions · Answer 2

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: 7156 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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: 3790 More actions · Answer 7

heb1014

Hall of Fame

Points: 3790

April 27, 2011 at 10:49 am

#1316755

Vishal,

Please go ahead and post your code.

UMG Developer SSChampion Points: 13482 More actions · Answer 8

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

UMG Developer SSChampion Points: 13482 More actions · Answer 9

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: 259085 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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