Sql Server: How to Deny users access to linked servers

Question

I'd like to deny access to query linked servers to a group of users. I've put them in a role and assigned certain permissions to object level permissions to the that group. I'm at a loss after searching BOL and 'net how to DENY access to linked servers.

EDIT:

I decided to break out profiler to verify exactly what SSMS is calling when displaying linked servers and ensure that a DENY was issued on that system view/SP. Turns out it calls sys.servers, but Sql Server doesn't honor the ACL on this system view -- It's does to other system views (ex: sys.dm_db_index_physical_stats).

Answer 1

Afaik referencing a linked server is not controlled by access control lists (ACLs). In other words, you cannot GRANT/DENY/REVOKE permission to use a linked server. You can certainly control the permission to change a linked server via ALTER ANY LINKED SERVER permission.

This apparent lack of permission is because the linked servers are forwarding specific credentials to the remote server, controlled via the impersonation or the remote_logins settings associated with the linked server. The actual access control happens on the remote server, using the credentials associated with the linked server. So in order to deny a group of users access to the linked server, you need to deny that group access to the remote server on the remote server itself.

Answer 2

The type of authentication is of importance. When you link the servers they have to be linked using Kerberos and then you can control the access on the linked server. Otherwise your group will always be authenticated on the credentials specified in the link.