Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1821
  • Last Modified:

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.
0
RobNicholson
Asked:
RobNicholson
  • 5
  • 3
1 Solution
 
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
 
imitchieCommented:
That would be useful to try first.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
RobNicholsonAuthor 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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now