Linked Server Problem

Posted on 2009-02-18
Last Modified: 2012-05-06
Hello there.   I have a SQL Server on my machine.  I successfully link to a corporate database (BF-SQL3) using the sp_addlinkedsvr script and am able to see all tables associated with that server, as I am supposed to be.  However,  we are trying to migrate my SQL Server Database to another corporate wide server (BF-SQL1).  For some reason I cannot link the two!!!

-What MS SQL permissions do I need in order to allow this to happen?

Question by:suicehockey44
    LVL 57

    Expert Comment

    by:Raja Jegan R
    You can run SQL Scripts which will work perfectly..
    But if you use Stored Procedure, you need to enable MSDTC service and other set of things to make it work as given below:
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Kindly give me the exact error message you have obtained so that I can guide you on it.
    FYI, I worked on similar things before..

    Author Comment

    Thanks guys, I run the sp_addlinkedserver and that actually works!!  However, it will not allow me
    access to the tables, which are protected with a UN/PASS combo.  While running the
    sp_addlinkedsrvlogin I receive the following message:

    Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
    User does not have permission to perform this action.


    Author Comment

    BTW the script for that is:

    EXEC sp_addlinkedsrvlogin 'SERVER', 'FALSE' ,null, 'TEST_USER', '*******'


    Author Comment

    Another interesting thing is that Im Using SQL Server Express 2008 on my machine and the linked server is 2005.  
    I am reluctant to truly consider this an issue because whilst connect to my own machines server, both the aforementioned scripts work correctly. Hmm.

    Accepted Solution

    I believe I found a solution. If I hard code the SELECT statement from the linkserver.catalog.view I get the results, but for some reason, the objects themselves aren't visible in the GUI. Perhaps a compatability issue.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now