SSRS: Displaying User specific data on a report

on SQL Server Reporting Services, I have the below requirnment for a report

1. Users are authenticated for Reporting using开发者_如何学JAVA Active Directory (windows domain accounts)
2. We have an custom authentication DB and the data that should appear on the report are related to users on this Authentication DB
3. Need to generate a report that list Sales data for all the stores, and "all data" should only be available to "Admin" users

4. For "Non Admin" users, same report should only display sales data for the single store that they are associated with. (Not the whole list of sales data for all stores)

   • Definition of "Admin", "Non Admin" user categories are defined on the custom Authentication DB.

Really appreciate if someone could give me the directions on what approaches I should follow. Please let me know if I should further clarify my requirements.

Thanks.

In SSRS you can access the User ID (=User!UserID). Set this up as an internal parameter for the report, and pass this into your dataset queries as a parameter.

Now you can join to your custom permissions table in your dataset queries.

Use a query constructed with this method to populate your parameter drop down for "Store."

Also use the parameter again and double check that the user has permissions in your main query that returns results. This will avoid a user being able to manually specify a store that they don't have permissions for. (Otherwise URL access would allow users access to reports they shouldn't get.)

Let me know if any of this isn't making sense.