I have a query that I can run on my server (Server1) as follows:
SELECT * FROM master.sys.databases WHERE name = 'MyDB'
This returns a row.
I have a linked server setup on Server2 which points to Server1. The linked server's permissions are set as
"be made using the login's current security context" and there are no local to remote server mappings.
When I run the following query on Server2, I get no rows:
SELECT * FROM SERVER1.master.sys.databases WHERE name = 'MyDB'
For some reason, this is the one of two databases where this is happening, all the others (10+) show fine in the following remote query:
SELECT * FROM SERVER1.master.sys.databases
I don't understand why
sys.databases does not list the database "MyDB" when queried via a linked server.
Also, I am not sure if it makes a difference, but MyDB is mirrored (as are all databases on the server).