none
Unable to give permission on Azure SQL Data Warehouse catalog views and DMVs RRS feed

  • Question

  • Hi,<o:p></o:p>

    How to give a user permission to query sys objects like catalog views and DMVs in SQL Data Warehouse. We understand that to query a DMV we need VIEW SERVER STATE or VIEW DATABASE STATE permissions, but trying to give VIEW SERVER STATE permission returns error 'Securable class 'server' not supported in this version of SQL Server'. <o:p></o:p>

    And there is no option to give VIEW DATABASE STATE permission to query sys objects at database level. <o:p></o:p>

    Can anyone please assist?<o:p></o:p>

    Thanks in advance<o:p></o:p>

    Nirav<o:p></o:p>


    Tuesday, May 10, 2016 12:35 PM

Answers

  • Hi Nirav,

    DMV access should be through the user database. VIEW SERVER STATE is currently not a concept that is supported in SQLDW.

    The only current workaround right now is to assign CONTROL ON DATABASE:

    GRANT CONTROL ON DATABASE::userDatabase TO user.

    This experience is not ideal and we are working on adding support for GRANT VIEW DATABASE STATE.

    I hope that helps!

    Thursday, May 12, 2016 9:04 PM