How to grant rights on sys objects to a non admin user ? RRS feed

  • Question

  • use Master
    --Logged in as sa
    Create Login Test With Password='India*498'
    Create User Test For Login Test
    Grant Select on sys.dm_os_tasks to Test
    --Logged as test
    Select * from sys.dm_os_tasks
    --Error. The user does not have permission to perform this action.
    Monday, December 21, 2009 12:07 PM


All replies

  • Accessing system objects require specific permissions.
    For example sys.dm_os_tasks requires VIEW SERVER STATE permission on the server.

    So if you want user Test to query sys.dm_os_tasks you need to grant him "view server state"
    But  it will expose a lot of other system views to user.

    You can find information about permissions required to access system objects in BOL.
    Monday, December 21, 2009 1:15 PM
  • Thanks Kilyuk. So I cannot grant right to only a specific object. Right ?
    Tuesday, December 22, 2009 4:40 AM
  • You are right. 
    The proof in documentation is here http://msdn.microsoft.com/en-us/library/ms188754.aspx (section Required permissions)
    • Marked as answer by sqlblr Wednesday, December 23, 2009 5:40 AM
    Tuesday, December 22, 2009 8:44 AM
  • All the system DMVs can be viewed by a non-admin when he is granted with VIEW SERVER STATE PERMISSION.
    Alex is right.
    Thanks, Leks
    Tuesday, December 22, 2009 4:48 PM