Basically, is it possible to set the approle at the report's runtime? We have a couple reports that need to get data from a restricted table. We can make this work if we add the current approle (db_executor) to the necessary role to access said data, but then any report would have access. I have tried simply calling the sp_setapprole in the data set, but get an error telling me this can only be done at the ad hoc level. Changing the procedure's definition to use WITH EXECUTE AS OWNER works as well, but again I do not wish to handle it this way. Any help is greatly appreciated.

Hi dgjohnson, If you do not want the users to access all the reports, you can leverage the permission in reporting services to achieve this, for more detail information, please see: http://msdn.microsoft.com/en-us/library/aa337491.aspx Thanks,Challen Fu TechNet Community Support

Hello, Follow the thread below. It may help you. http://social.msdn.microsoft.com/Forums/zh/sqlsecurity/thread/a2718507-cf71-4736-bd84-729ef238e51f

Thank you both, but I have seen both of these already. We are already using application roles for our security, and the reports are already executing with the db_executor role. The specific issue we face now is credit card information. For security reasons, these tables are encrypted. We have a special role that has access to view this information, and db_executor does not have access to this role. We tested by adding the db_executor role to this other one, and it worked as expected. However, we do not want this role to have that access, but would rather specifically set certain reports to use a different role, which does have access. As mentioned in the thread linked by phil, I tried adding the call to sp_setapprole to the query being executed by the report, but get the error stating that sp_setapprole can only be done at the ad hoc level. So, perhaps another way to solve my issue would be to know how to get SSRS to run an ad hoc query prior to executing the main dataset.