Linked server available to users without permissions

Question

I have users seeing a linked server which they are not supposed to see.

The linked server is defined so that only I will have access to it, but everybody can see and use it.

I have created the new linked server using the following steps:

1. Connect SSMS Object Explorer to the SQL Server instance
2. Expand Server Objects, right click Linked Servers and left-click New Linked Server...
3. Under General tab choose SQL Server for Server type and write the Server's name
4. Under Security tab in the upper part click Add, choose "sa" as Local Login, enter Remote User's name and Remote Password
5. Under Security tab in the lower part (under For a login not defined in the list above, connections will:) choose the first option: Not be made
6. Click OK and start testing

Now the only person supposed to see the linked server is me ("sa"), but somehow other users can see it and use it.

Note 1: The users who can use the linked server have permissions on the remote server, they are not seeing data that they should not see, they are just able to access it from the linked server when they should not be able to.

Note 2: I am the only sysadmin on both instances.

Answer 1

There is no way to "hide" a linked server or prevent unauthorized users from trying to use it. All you can control is whether they actually have access on the other side. A linked server in and of itself is not an actual object; like a synonym, you don't grant access to the alias, just to the thing(s) it references.

Try it out, and you will see that some of your assumptions (e.g. that "a login ... can not see the linked server in object explorer") are false. I did this:

1. Created a login, and granted no privileges whatsoever, just CONNECT / public role.
2. Created a linked server, and did not add this login to the list of logins.
3. Opened a new instance of Management Studio, connecting with this login.
4. I was able to see all linked servers under Server Objects > Linked Servers.
5. I was also able to query sys.servers to see the list of linked servers.

I was able to run the following query no problem:

SELECT name FROM [linked server].master.sys.objects;

However I was unable to run queries against non-system databases, since I had not granted explicit access to any non-system database or the objects inside. E.g.:

SELECT SalesOrderID FROM [linked server].AdventureWorks2012.Sales.SalesOrderHeader;

Yielded this error:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "linked server" does not contain the table ""AdventureWorks2012"."Sales"."SalesOrderHeader"". The table either does not exist or the current user does not have permissions on that table.

I expect that you can achieve more restrictive results by explicitly denying access to specific objects on the linked server (or by DENY CONNECT SQL to that login altogether), but this does not limit its effect to the linked server; it also affects direct connections.

In any case, I fail to see the point of what you're trying to accomplish:

1. If the user doesn't have access on the other side, what is the harm in letting them try? (You might have better luck telling them not to and auditing them than trying to prevent them from trying in the first place.)
2. If the user does have access on the other side, why can't they use the linked server?

Answer 2

You can use the first option "Not be made with" a login mapping like below:

Here's the result with the mapped login:

And here's what happens with another account