Hi guys,
I have a question on restricting a data using some kind of dynamic parameter when users are using Microsoft Reporting Services Report Builder. I saw that I can set permissions and restrictions for report model items like columns, but I want the users be able to see all the columns, but the actual data that gets pulled needs to be restricted using a database table (bridge table) that assigns the users to what they are able to see. Is there anyway that the report builder can restrict the data itself using windows authentication or login so that the users can able to see their data and not others while all users having access to the same model and same columns.
Any help is appreciated.
Enkh.
Restricting data in Report Builder
Devteam
You can use security filters in the report model to do this. It is not actually stored in a bridge table like what you're mentioning, but it is based on the user and an expression that is evaluated at the time of running the report.
You can read about it here: http://msdn2.microsoft.com/en-us/library/ms365305.aspx.
The other solution is to use Windows integrated secuirty to access the data source and to build your model on top of views which return different data based on the user that is accessing them. Using windows security in this way requires Kerberos to be enabled, which sometimes is a non-starter.
Hope that helps,
-Lukasz
JaseT
Lukasz,
Thanks for your quick info and reply. The first way of doing this is actually I had to create permissions and actually enter the users Windows login information to give this. So I had to add user1, user2 etc. to give them the data that they should only able to see. I still don't understand how "GetUserID()" function works. I mean when a user opens up report builder and it uses Windows authentication, what is getting returned from GetUserID() Is it the windows username
Ok, my situation is that there are a lot of users in the hundreds range. My main two questions are:
1. What is getting returned by "GetUserID()" function What is the user's user ID in this case
2. Do I manually have to add the users to the permission table of the model to give them rights Or this is automatically happens and I don't have to touch a thing because i'm using GetUserID() and the function is magically checking the user's windows authentication information with the filter that I applied to the column
3. If I want to do it though the data source, how can I do it and as you said also with Kerberos I would love to know how to use datasources and the actual dataview is filtering out the data based on the user's information that I don't have to add and update it on the permissions table I would love to have the dataview filter out the information right then and there instead of doing a filter at the report model level
Thanks for your help,
Enkh