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:
- Connect SSMS Object Explorer to the SQL Server instance
- Expand
Server Objects
, right clickLinked Servers
and left-clickNew Linked Server...
- Under
General
tab chooseSQL Server
forServer type
and write the Server's name - Under
Security
tab in the upper part clickAdd
, choose "sa" asLocal Login
, enterRemote User
's name andRemote Password
- Under
Security
tab in the lower part (underFor a login not defined in the list above, connections will:
) choose the first option:Not be made
- 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.
Best Answer
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:
sys.servers
to see the list of linked servers.I was able to run the following query no problem:
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.:
Yielded this error:
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: