Post reply

Linked Server missing tables

  • July 6, 2012 at 8:43 am

    #277407

    Hi All,

    On my dev machine (laptop) I'm trying to connect several servers to my dev machine using Linked Servers. When setting up the linked server I set the options (under security) "Be made using this security context". I've entered a userid and password which I also set up on all the linked servers I need access to. The problem is I have one linked server that does not display many of the databases in the server. And only one of the databases will display any tables within its catalogs.

    Steps I've taken.

    1. Made sure userId and password are correct on the linked server.

    2. Make sure the userid has permissions to the db's I need access to.

    I setup 4 other servers the same way, and for some reason only this one is giving me a problem. Any ideas.

    Thanks

  • July 6, 2012 at 11:18 am

    #1509816

    by default, a linked server will only show the objects available in the DEFAULT DATABASE of the loginthat is connecting;

    That's important, because if you have a login 'bob' that has rights to , say the database SANDBOX, but the default database for the login is master, then when you run EXEC sp_tables_ex myLinkedServer (which is what the GUI in SSMS calls), you'd probably see nothing,even though a fully qualified select like SELECT * FROM myLinkedServer.SandBox.Dbo.Invoices will work.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply