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).

  • What happens when you connect to SERVER1 and run SELECT * FROM master.sys.databases? – Randi Vertongen Apr 23 '19 at 16:19
  • 1
    Is it possible the database has trailing spaces or other non-printing characters embedded in the name? You should also always use the N prefix on string literals that could contain Unicode data. What does this give: SELECT name, ASCII(RIGHT(name,1)), UNICODE(RIGHT(name,1)) FROM SERVER1.master.sys.databases;? Also what exact type of login are you using for the current login's security context? Are you doing this from the same machine, the same instance of SSMS, etc.? The fact that it works on the server but not through the linked server tells me it's either translation or permissions. – Aaron Bertrand Apr 23 '19 at 18:30
  • 1
    did your test connection to linked server succeeded on server1? <SELECT * FROM [YOURLINKEDSERVER].master.sys.databases> this should work as expected if your linked server working fine. – CR241 Apr 23 '19 at 21:42
  • I recreated the linked server and all seems fine, although I can't promise I wasn't doing something stupid... – SEarle1986 Apr 24 '19 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.