I have a linked server (Server B) set up on Server A with permissions set as follows:
This means that there are five Windows Logins (of which my windows account is one) who can access the linked server on Server A and the permissions they will have when using the linked server are the permissions they have set on Server B.
If I log into SSMS as myself on my local machine (which isn't the server), connect the object explorer to Server A, right click on the Server B linked server and select "Test connection" I get the following error:
However, when I run the following code on Server A, the command completes successfully:
DECLARE @name SYSNAME = 'ServerB' EXEC sp_testlinkedserver @servername = @name
I can also verify that the linked server works by querying it as below:
SELECT TOP 1 1 FROM [ServerB].[MyDb].[dbo].[MyTable]
1 as expected.
Why does the connection test in the GUI fail when clearly everything is working as it should be?