Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
Well you can't really hide the linked server, but when they try to use it, if their current login context doesn't have any permissions at the other end... – Aaron Bertrand Jun 2 '13 at 17:35
    
@AaronBertrand You can hide a linked server simply by not providing access to it. A login who is not defined in the list of allowed logins and is not a sysadmin can not see the linked server in the object explorer. – Roi Gavish Jun 3 '13 at 7:11
    
@AaronBertrand If their current login context does have permissions at the other end, they are still not supposed to be able to connect via the linked server if not allowed to. – Roi Gavish Jun 3 '13 at 7:14
    
No, you can't hide the linked server that way. Have you tried this? – Aaron Bertrand Jun 3 '13 at 14:42

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?
share|improve this answer

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

enter image description here

Here's the result with the mapped login:

enter image description here

And here's what happens with another account

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.