asked
The server principal is not able to access the database under the current security context.
I get this error executing a stored procedure that goes through a linked server then goes through another linked server. So, three hops. my login is a SQL authenticated login and the version of SQL is 2014. I have read a lot of articles on the web and most of them are for SQL 2005 or Windows authenticated logins. Any suggestions with a different twist on an old error? thank you
How are the linked servers configured in regards to permissions? Also, probably a bad idea to do three hops through linked servers. Why not just do one directly to the server needed? Of course, I'm not a huge fan of linked servers to begin with but much like cursors they have a time and place to be utilized.
JohnM. Again thank you for your help with this issue. Turns out the developer said the third hop was to one dB but it was really to another dB. So we were granting access to the wrong dB. Lesson learned, don't trust what the developer says, look at the stored procedure yourself,
JohnM - thank you for getting back to me. The linked servers security is "made using the login's current security context". I hope that is what you mean. The login we are using exists on all three instances. Additionally, this works if I make the login a Sysadmin on all three instances. But as a db_owner it gives the error on the third instance. I agree the three hops are a bad idea, but that is the development side of the house and not the best practices were previously established.
It's been a while, but I think you a have to have a mapped login for this to work.
https://technet.microsoft.com/en-us/library/ms175537%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
19 People are following this question.
