7

I need to give a database user read access to the sys.master_files table. How can I do that?

Currently the user has this permissions:

enter image description here

Calling SELECT on sys.master_files returns an empty result. I also tested the same query with the sa user which works as expected.

Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
BetaRide
  • 16,207
  • 29
  • 99
  • 177
  • 1
    The [relevant Technet documentation](http://technet.microsoft.com/en-us/library/ms186782.aspx) - available **freely** to anyone - clearly states what permissions are needed for the `sys.master_files` catalog **view**: *The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.* – marc_s Sep 20 '13 at 08:33
  • Yes, I know, but I don't know how to apply this rights in the GUI. – BetaRide Sep 20 '13 at 08:45
  • 3
    They are server level permissions, not database level permissions, click the securables section in the screen you were in when you took the above screenshot and you will see them. – steoleary Sep 20 '13 at 09:45
  • Thanks! Feal free to add your comment as an answer in order I can accept the anser. – BetaRide Sep 20 '13 at 10:08

2 Answers2

10

for you to run successfully

select * from sys.master_files

the minimal permissions that you need to grant is as follows:

GRANT VIEW ANY DEFINITION TO [texas_user]
GO

I have tested and it works fine on sql 2008 r2

regards
marcelo
Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
0

Those server level permissions can also be added by T-SQL script such as :

use master
grant ALTER ANY DATABASE  to [texas_user]
grant VIEW ANY DEFINITION to [texas_user]
grant CREATE ANY DATABASE to [texas_user]

The result will be the same: user texas_user will be able to receive results from the sys.master_files. But probably those abilities are some much more than you want. May be, you can receive the same results querying the sys.database_files in the user databases (texas, TexasLog).

Sandr
  • 776
  • 1
  • 6
  • 10