SQL Clone
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
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 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 Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

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



SQL Tips and Scripts
SQLWorks Blog
heb1014
heb1014
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 486
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 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 (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 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
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 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
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 486
Vishal,

Please go ahead and post your code.
UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

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

Group: General Forum Members
Points: 3914 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