Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cabradley
Starting Member
5 Posts |
 Posted - 2009-10-08 : 06:09:09
|
| Hi,I have developed a number of reports which have a parameter for the location.I have some users who should see all options in the parameter (e.g. location A, location B and location C).I have some other users who should only have permission to run the report for a specific location (e.g. location A).I can rewrite the aspx page so that the list of parameters is only showing the correct locations which the user has permissions on.However if the user knew the correct URL for a report which they do not have permissions on, they could still run the report as at the report server they have permissions on that report.Is it possible to set permissions at the server level so that users only have permissions to run a report with specific parameters, and not with other parameters, - They can run for location A, but not for location B or C?Thanks very muchClare |
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-08 : 06:41:28
|
| I am assuming there is a table of User to Valid Locations that determines who can see what?Reporting SQL should have a WHERE LOCN IN (@LOCN) and then use a second data set that determines the locations available based on the user and use this to drive the drop down list.John |
 |
|
|
cabradley
Starting Member
5 Posts |
Posted - 2009-10-11 : 07:43:42
|
| hi John,Thanks for your response. Yes i can work out how to display the correct options in a dropdown lists in an aspx page, but i dont think this will actually prevent the user having access to a report run for a different location. For example if a user from location A gives the report URL to a user from location B - they would still have access at the report server level to see the report for a different location - can this be prevented?Clare |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-12 : 09:44:18
|
| Clare, yes, In no particualr order- Join the table mentioned above that has the User/Location so they can be referenced in a Where clause- In SQL add a line similar toWHERE ('NY_WNY\' + ED_Users.Rn_Descriptor = @UserID) AND (@UserLocn = LocnFile.Locn) (Since windows includes the hierarchy of organizational units your example will be different in the quotes)- In the report parameters, define a parameter called @UserId in SQL and just UserId in Report. For the report parameter definition of UserId select Hidden, and make the default = "=User!UserID" This way it selects the valid location of the user who is signed on.John |
|
|
|
|
|
|
|
|
Set permissions on reports depending on user login