locked
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

Answers

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"
    GRANT VIEW SERVER STATE TO Test
    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