Solved

SQL Server linked server permissions issue

Posted on 2007-12-05
8
1,680 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
  • 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
 

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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