I've created view to query another server via linked server(SQL Server 2014).
I need to grant permissions to developers to use this view.
Granting only select is not enough and they got the following error:
Access to the remote server is denied because no login-mapping exists.
What is the most restricted permission that I can grant them to be able to select from this view?
We use a SQL login to connect to linked server (be made using this security context), and I can query this view with my user. I can also query this view with my Active Directory user. I didn't try to connect to access the linked server with linked server login, but my AD user couldn't connect to linked server, if there were any issues with connecting login.
For testing purposes, I granted sysadmin to this user. It could query this view, so it is definitely a permissions issue. Prior to granting sysadmin, the user had only public, with no special permissions at the server level and only db_reader in the view's database.