asked on

Linked Server Problem

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?

Thanks!

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:

http://support.microsoft.com/kb/839279

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

suicehockey44

ASKER

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.

suicehockey44

ASKER

BTW the script for that is:

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

suicehockey44

ASKER

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.

ASKER CERTIFIED SOLUTION

suicehockey44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial