Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Setup Report Parameters to Default Based On User Credentials


How to Setup Report Parameters to Default Based On User Credentials

Author
Message
Jody Claggett-376930
Jody Claggett-376930
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 199
Comments posted to this topic are about the item How to Setup Report Parameters to Default Based On User Credentials

_______________________________
[size="5"]Jody Claggett
SQL Server Reporting Analyst
[/size]
SQLWorks
SQLWorks
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 195
Is anyone capturing credentials from active directory without the need for a users table?



SQL Tips and Scripts
SQLWorks Blog
heb1014
heb1014
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 455
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
mtillman-921105
Say Hey Kid
Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)Say Hey Kid (704 reputation)

Group: General Forum Members
Points: 704 Visits: 3852
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
fabauer
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
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
deanroush
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 265
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.
MVMDataStrategy
MVMDataStrategy
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 49
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
heb1014
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 455
Vishal,

Please go ahead and post your code.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2408 Visits: 2204
Thanks for the article, it was well written and expands my knowledge of Reporting Services.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2408 Visits: 2204
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search