Reporting Services Security
-
We use a reporting product called Actuate. Actuate has a feature called page-level security.
Basically, page-level security works like this:
A user would login to view reports. The user's login id would also be stored in a database table that is used to produce a report. In Actuate, a property (page-level security) may be set so that only the report pages for the loged in user are visible to that user.
For example, we may have a sales report that lists information by sales rep for all of our sales reps. A sales rep would login to view the sales report, but only see his/her pages.
Is there something comparable to this in Reporting Services?
Thanks.
-
Hey Patrick,
I have run into the same issue with Sales Reps and other people. We want to create a report where they can just see their data but obviously want to minimize maintenance.
RS has a global variable called "User!UserID". What I do is setup a Report parameter, say network_ID, and set it equal to User!UserID. I then add this report parameter to my where clause and there you go. At run time this will only return results relevant to the user running it. You may need to have a look-up table to map network IDs to sales rep.
SELECT * FROM sales_table WHERE sales_rep = @network_ID
Hope this helps...
-Mike
-
Thanks, Mike! Your solution should work for us.
-
This will work for on demand reporting but are you planning on doing History Snapshots in SSRS?
-
Thanks for your reply, David.
I don't yet know the answer to your question regarding history snapshots.
Can you suggest an alternative method in RS for page-level security?
-
If you retrun the userId id the result set for the query, you can use the User!UserId variable to filter the data set. This will work for on demand reporting. However, this may cause issues if your users are trying to run subscriptions as they do not run under the user's account but as NT Authority.
Also, I did quite a bit of playing with trying to run a single snap shot and then give all users access to it. Using the above, the report should filter based on the user's id so they only see the data they have rights to. As you said, this was simple to do using Actuate. What I found though in SSRS, the solution I sued was somewhat flaky. It worked maybe 50% of the time and when it did error out, it just gave an idex outside the bounds of the array error.
We eventually abandoned this idea and I have not gone back lately to see if this works in SP2 or not. There's a lot I like in SSRS but Actuate did make your life easier for these types of solutions.
Dave
-
I haven't played with snapshots all that much so I can't comment on that. As for subscriptions there are 3 options that I am aware of:
1) If you are running SQL Server Enterprise (can't blame you if your not) then you can create custom tables for subscriptions to use. Here you could obviously setup the correct user ID. The other benefit of this solution is that you can set it up in a fashion that will not send out blank reports if there is no relevant data (assuming you don't want it sent...another shortcoming of SSRS)
2) You can run an update script against the subscriptions table and replace the parameter with the correct user ID
3)The user can enter their user name in the parameter box when setting up the report.
Option 1 isn't that bad (again assuming your running Enterprise) and actually can be beneficial since you have more control but requires a lot more work. The other 2 options total stink...but hey if they made it easy IT wouldn't be any fun
-Mike
-
Thanks for your suggestions, Mike. We are running the Standard edition, so we can't use option 1.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply