Querying sys.master_files through a linked server, I get no results.

Why? How do I work around this issue?

  • You may want to provide a little bit more details before your question is closed. What do you mean by "no results", for example? – mustaccio Sep 2 '15 at 21:07
  • 2
    No results means the number of returned rows was zero. There are no more details to provide and I can't see a good reason to close since others understood the problem immediately and have been able to point me into the right direction. – Magier Sep 3 '15 at 7:29

This approach works fine for me:

SELECT * FROM [linked_server_name].master.sys.master_files;

What you need to be sure of is that the linked server is running in the context of a user who has enough privileges to see the contents of the DMV. You can check who you're executing as using:

EXEC [linked server name].master.sys.sp_executesql N'SELECT SUSER_SNAME(),
| improve this answer | |
  • your hint seems to lead into the right direction. It's a matter of permission of the Linked Server User context. TY. I can't figure out the permission I have to add. BTW, the view exists in every db, so it looks like it's not required to use the master db. I want to prevent setting permissions on single objects if not really required. Is there a db role suitable for this? – Magier Sep 3 '15 at 7:02
  • 1
    I applied USE master; GRANT VIEW ANY DEFINITION TO SqlDbaController GO and this made it work. TY. – Magier Sep 3 '15 at 7:25

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.