Error on Linked Server SQL state 42000 error 7314

This morning started with a bit of a nuisance. I got a message from one of our customers that a night job failed with an error.

First thing i checked after logging in was if the database and table exist on the server. Because the error states that de table doesn’t exist on the database or that there are insufficient rights. The table exists and the user to connect to the database has the rights to do so.

Next step is to impersonate the account. Let’s see if the user can actually access the database through the linked server.

execute as login = 'sql'

select * from server.database.schema.table

revert

This resulted in the same error. Either the table doesn’t exist or there are insufficient rights. The next step for me was to check the configuration of the linked server. More specifically, the security. Because the account that’s used to set up the linked server needs rights on the server it’s linking to.

In my case, the user shown above (not the actual user) didn’t have any rights whatsoever. Just public. And with that, it can’t do a thing. Because the connection to the data will be made with this user.

The fix was to grant rights to the linked server user to the database it needed to get access to. The job ran, customer happy and i re-learned that you have to be carefull in your linked server setup.