SQL Server linked server permissions issue
Posted on 2007-12-05
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.'
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.