Solved

SQL Server linked server permissions issue

Posted on 2007-12-05
8
1,731 Views
Last Modified: 2008-03-22
I've just started a job where I have inherited a spaghetti nest (undocumented!) of linked servers with
stored procedures and jobs being done across servers.

One of these jobs (it runs a stored procedure that updates a table in a db on the linked server) is
failing with an:
 'Executed as user: domain\sqlserviceuser. OLE DB provider 'servername' does not contain table
'"db_name"."table_name"."column_name"'.  The table either does not exist or the current user does not
have permissions on that table. [SQLSTATE 42000] (Error 7314)  OLE DB error trace [Non-interface error:  OLE DB provider does not contain the table: ProviderName='servername',
TableName='"db_name"."table_name"."column_name"']. [SQLSTATE 01000] (Error 7300).  The step failed.'
error.

Clearly this is likely to be a permissions issue. Services on both servers are running under the
domain\sqlserviceuser' account. The linked server configuration specifies a local login that does have
the necessary permission to update the table on the remote server.
0
Comment
Question by:RobNicholson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20410382
Executed as user: domain\sqlserviceuser
seems to be at odds with
The linked server configuration specifies a local login

can you recheck sysservers?
0
 

Author Comment

by:RobNicholson
ID: 20410411
The linked server is there although curiously the datsource column is 'null' and the srvproduct column is an empty string. Maybe I need to delete and recreate the linked server?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20410440
That would be useful to try first.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RobNicholson
ID: 20410595
Yes, still the same.

Does a linked server not need the datasouce to be in the sysservers table? Maybe that is the problem?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20410772
Is this the actual error text?

[ does not contain table '"db_name"."table_name"."column_name"'. ]

IT looks terribly suspicious. shouldn't it be db_name.tablespace.table_name ?
0
 

Author Comment

by:RobNicholson
ID: 20410924
You are right - sorry my mistake when I copied the error. It is:

OLE DB provider 'servername' does not contain table '"db_name"."table_owner"."table_name"'.  The table either does not exist or the current user does not have permissions on that table.
0
 

Author Comment

by:RobNicholson
ID: 20426122
Still no solution - I think the key maybe the fact that sysservers shows the datsource column is 'null' and the srvproduct column is an empty string although I have deleted and recreated the linked server.
0
 

Accepted Solution

by:
RobNicholson earned 0 total points
ID: 20581120
I have changed the stored procedure and am now getting a different error - probably outwith the scope of this question so I will submit a new one if necessary. In the meantime this can be closed with no solution supplied and no points awarded
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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