I need help setting up security division between SELECTing and EXECUTEing from views and stored procedures and access to base tables referenced by those objects on linked servers on SQL Server 2012SP3. We have a number of production application databases in use, and use a centralized reporting server to query against the production databases for our reporting needs. Our reporting account should be able to select from and execute views and stored procedures, but not query the linked server base tables directly.
As I understand it, if you would have the view or SP on the same database as the base table, you would use ownership to give the view or SP the ability to query against a table while giving a reporting account priviliges to select from the view and not from the base table. This does not seem so straightforward with a linked server. I've tried a number of things to emulate this behaviour, such as giving the view owner (sysadmin role) a logging mapping for the linked server, but this results in the following error when selecting from a view:
Msg 7416, Level 16, State 1, Procedure ProcedureName, Line 7 [Batch Start Line 0] Access to the remote server is denied because no login-mapping exists.
Other ideas I've had was to add a logging mapping for the reporting login that is mapped to a very limited account on the production databases, but wouldn't this block the functionality of the views and SPs as well? Since if I read it right the warning above indicates it's running the select statement in the security context of the reporting acocunt - which is confusing, since then blocking access to the dbo schema does not prohibit it form select from that linked servers tables. Mark me as confused.
I've also tried EXECUTE AS with the stored procedures, to force the use of the mapped sysadmin account. Using this results in the same warning as the one above. I've read that this might be related to a bug regarding user mapping, as described here: https://stackoverflow.com/questions/32084453/sql-linked-server-yields-error-no-login-mapping-exists-if-non-admin-account-is . However, the schema owner mapped in the linked server has the sysadmin role.
Any ideas on how to tackle this issue?