August 16, 2016 at 10:47 am
Hi!
I'm running into an issue trying to access a synonym. What I have is:
"Server A", which has a "MyDatabase" database on it, which includes a table called "MyTable"
"Server B", which also has a "MyDatabase" database on it with a table called "MyTable"
Basically, we've moved MyDatabase and MyTable from "Server A" to "Server B", but we can't remove MyTable from "Server A" because many of our stored procedures and views on various servers are still accessing it through "Server A" via linked servers.
Since updating those sprocs and views will take some time and testing, what we were hoping to do is create a synonym on MyDatabase on "Server A" that would reference MyTable on "Server B". Since the most up-to-date information is being stored on "Server B", this would spare us from having to keep importing data from the more recent MyTable on "Server B" to the older one on "Server A".
Unfortunately, when I create the synonym on "Server A" to point to MyTable on "Server B" and try to access this via a query against MyDatabase on "Server B", I get the following error:
"Cannot process the object MyTable. The OLE DB provider "SQLNCLI11" for linked server "Server A" indicates that either the object has no columns or the current user does not have permissions on that object."
I've reviewed the permissions carefully for the synonym and the linked server we have set up to go between the two servers. Is there a known limitation when trying to use synonyms across servers?
Thank you for any assistance!
August 17, 2016 at 1:05 pm
It's doable but there are oddball issues that come up like yours. In some cases, I've seen where just adding set nocount on for the procedure or query has cleared up this error.
Also in your query try selecting just one column and filter it to one row to see if this makes a difference for now.
Sue
August 17, 2016 at 1:20 pm
tarr94 (8/16/2016)
I've reviewed the permissions carefully for the synonym and the linked server we have set up to go between the two servers.
You've reviewed permissions for the synonym, but have you also looked at the permissions in the database you're referencing through the synonym? Check the permissions of the user associated with the login you're using to connect from the calling server to the called server. The user in the called database has to have permissions to the objects you're trying to use.
I hope this made sense and didn't sound too twisted.
August 18, 2016 at 3:25 pm
Thank you both of you for your responses. I'll give these a try and see if they make a difference!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply