RobNicholson
asked on
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"."c
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"."tabl
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.
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"."c
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"."tabl
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.
ASKER
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?
That would be useful to try first.
ASKER
Yes, still the same.
Does a linked server not need the datasouce to be in the sysservers table? Maybe that is the problem?
Does a linked server not need the datasouce to be in the sysservers table? Maybe that is the problem?
Is this the actual error text?
[ does not contain table '"db_name"."table_name"."c
IT looks terribly suspicious. shouldn't it be db_name.tablespace.table_n
[ does not contain table '"db_name"."table_name"."c
IT looks terribly suspicious. shouldn't it be db_name.tablespace.table_n
ASKER
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"."
OLE DB provider 'servername' does not contain table '"db_name"."table_owner"."
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
seems to be at odds with
The linked server configuration specifies a local login
can you recheck sysservers?