Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
    
Did you consider the option "using the current login's security context"? –  Aaron Bertrand Jun 2 '13 at 15:43
    
@AaronBertrand That will not prevent the use of the Linked-Server, it will just change my choice to comply with the results :) –  Justicator Jun 2 '13 at 15:46
    
@AaronBertrand I have already found the answer, I'm going to add it as soon as I find a few minutes to spare :) btw, congratulations for your brand new diamond :) –  Justicator Jun 2 '13 at 15:49
    
Of course everybody is still welcome to answer, maybe You will have a better answer than mine, or just be quicker... –  Justicator Jun 2 '13 at 15:52
    
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

1 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?
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.