Linked Servers no longer working in my Default Instance

  • Greetings all,

    So last week, I was attempting to give our company report builder access to use a linked server to an excel file on the same server as the SQL Server (Yes I came up with a much better solution, but this is the back story to the problem).

    I was able to use the linked server, but he was not, and after several attempts to give him access (I admit I have never granted security to a linked server object before and was going by trial and error). At one point I assigned him to the 'setupadmin' role figuring that if he had the ability to create linked servers, surely he would be able to ACCESS them! Still didn't work, but after I removed that off of his login, I was unable to connect to any of my linked servers.

    I know I didn't mess up the Server itself, as the SSIS ETL process can still access remote connections (such as our as400), however when attempting to use SSMS I get linked server memory errors whether I am using an IBM OLE DB connection, or an Excel connection.

    On a hunch I decided to install a named instance, and sure enough I can connect fine to these sources on the Named instance, but still nothing on the default instance.

    Did I mess something up when adding the setupadmin role and then removing it?

    Link to my blog http://notyelf.com/

  • No ideas on this at all?!

    Wow I like blowing stuff up so well that even the experts are silent 🙂

    Link to my blog http://notyelf.com/

  • Hi,

    What are the security properties of the linked server? What message is he viewing when he is triying to access?

    Regards,

    Jose Santiago Oyervides.

  • It's not him anymore that is the problem it is ME...the administrator to the entire server...

    I was able to resolve his issue after some research but it was after I dropped him from the setupadmin role that everything fell apart.

    Link to my blog http://notyelf.com/

Viewing 4 posts - 1 through 3 (of 3 total)

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