SQL Server linked server permissions issue

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.
RobNicholsonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
RobNicholsonConnect With a Mentor Author Commented:
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
 
imitchieCommented:
Executed as user: domain\sqlserviceuser
seems to be at odds with
The linked server configuration specifies a local login

can you recheck sysservers?
0
 
RobNicholsonAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
imitchieCommented:
That would be useful to try first.
0
 
RobNicholsonAuthor Commented:
Yes, still the same.

Does a linked server not need the datasouce to be in the sysservers table? Maybe that is the problem?
0
 
imitchieCommented:
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
 
RobNicholsonAuthor Commented:
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
 
RobNicholsonAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.