Need help - linked server problems

[katbear]

Hi,

I set up a linked server.

On the local server, in Enterprise Manager, linked server properties, security, I have four choices:

1.) Not be made
2.) Be made w/o using a security context
3.) Be made with the login's current security context
4.) Be made using this security context (type in login/pwd)

Basically, ALL I want is to use a SQL Server login on the remote server to run the queries on the remote server. I thought that using choice #4, in addition to running sp_addlinkedserverlogin would do the trick.

But I am getting errors that it can't perform the query on the remote server.

What am I doing wrong here?

Thanks

 

[katbear]

The local server is a 2000 server. The remote (linked) server is a 2005 server. I have set up security to use "Be made with this security context" (remote login/pwd) - shouldn't that take care of the mapping?

This is the error I receive when I try to run a query against the remote server:


Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'sql100' does not contain table '"admin"."dbo"."mytable"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='sql100', TableName='"admin"."dbo"."mytable"'].

 

[ptheriault]

Are you using the 4 part naming convention.

SELECT *
FROM linkserver_name.DB_NAME.dbo.Table


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

You need to make sure that the user you specified for the linked server (in 2000) has permissions to the database and tables that you want to use in the 2005 server.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[katbear]

yes i am using the four part

 

[katbear]

found the problem

the remote user did not have permissions in the remote database

i am psyching myself out here... i knew i set this up right on the linked server end

 

[gmmastros]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

katbear,

I'm curious to know if my advice in this thread thread183-1346680 has helped.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[katbear]

George, as far as the other thread goes, a workaround was found involving not using the inline function or something like that, I will look at more closely when I have a free moment

Thanks