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

Here is a workaround I just thought of:

On Linked Server Properties dialog box, Properties tab; add users that you don't want to access the linked server. Then, assign them some dummy user/pass combination that will be rejected on target server.

Answer 2

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.