Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Server linked server permissions issue

Posted on 2007-12-05
8
Medium Priority
?
1,871 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

589 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