First poster, long time lurker here. What is the best way to activate application role in a report ?
I've tried different things and so far the only method that works is to embed the call to the application role like so :-
EXEC sp_setapprole 'REPORTZ', 's3cr3t'; select * from mytable where ID < 10000
in the Dataset. It does work... but not to my liking (certainly not in the shape I'd want to roll into production environment).
I'd prefer if I could somehow 'hijack' or 'inject' the application role activation line at runtime either through custom assemblies or probably some sort of 'server hook' in the Reporting Service (which in both cases, I have no idea how)
Much appreciated for your time + kind attention.