Most restricting permissions for querying view with linked server

Question

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.

Answer 1

The message you are getting is telling you that there is no mapping between the credentials you have supplied and credentials on the target machine. You need to ensure that the credentials you use to setup your linked server also has a login or can be mapped to a login on the linked server.

I suspect that when you set it up you specified the option Be made using the login's current security context which worked for you as you have a login on the remote machine - but it doesn't work for anyone else because they do not have sufficient credentials to query the linked server. (Just a hunch)

Here is a guide on how to set up a linked server and what all of the options are for, I suspect you may need to change the security context you are using to connect to the linked server or add the other accounts to it.

https://msdn.microsoft.com/en-us/library/ff772782.aspx

Answer 2

Recreating linked server without @provstr=N'ApplicationIntent=ReadOnly'

parameter solved no login-mapping existserror. So select permission is enough.