Sp who2 command details like

minimum permission to run sp_who2, queries against dm views and activity monitor

SQL Server Database Enginehttps: //social.technet.microsoft.com/Forums/en-US/1eee5121-a02a-42e2-9f96-872a7e69a921/minimum-permission-to-run-spwho2-queries-against-dm-views-and -activity-monitorQuestion49 / 14/2010 3:22:43 PM10 / 4/2018 7:02:29 PM Any questions or discussions relating to the SQL Server Database Engine, performance, features, Full-text Search, linked servers, etc.41
  • Question

  • text / html9 / 14/2010 3:22:43 PMdb0421880

    Hi. I'm trying to explain some suspended tasks that could be tied to blocks on a remote server source. I want just enough permissions to run sp_who2, activity ssms monitor and the data mgt view query you see below. What permission should I request minimally to do this? We're on STD 2008.

    SELECT

    *,

     

    SUBSTRING (st.text, (r.statement_start_offset / 2) +1,

     

    ((CASE r.statement_end_offset

     

     

    WHEN-1 THENDATALENGTH (st.text)

     

    ELSE r.statement_end_offset

     

     

    END- r.statement_start_offset) / 2) + 1) AS statement_text

    FROM

    sys.dm_exec_requestsAS r

    CROSS

    APPLYsys.dm_exec_sql_text (r.sql_handle) AS st

     

     

      

    • Edited bydb042188 Tuesday, September 14, 2010 9:21 PM changed title to imply one permission that fits all

Answers

  • text / html9 / 14/2010 3:28:18 PMGURSETHI4

    Hi,

    For SP_WHO as per Books On Line below permissions need to be there.

    Permissions
    The user will need VIEW SERVER STATE permission.

    Grade:
    If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

    For DMVs below permissions are required as per Books On Line.

    To query a dynamic management view or function requires SELECT permission on object
    and VIEW SERVER STATE or VIEW DATABASE STATE permission. This lets you selectively
    restrict access of a user or login to dynamic management views and functions. To do
    this, first create the user in master and then deny the user SELECT permission on
    the dynamic management views or functions that you do not want them to access.
    After this, the user cannot select from these dynamic management views or functions,
    regardless of the database context of the user.


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/
    • Marked as answer bydb042188 Wednesday September 15, 2010 7:12 PM

All replies

  • text / html9 / 14/2010 3:28:18 PMGURSETHI4

    Hi,

    For SP_WHO as per Books On Line below permissions need to be there.

    Permissions
    The user will need VIEW SERVER STATE permission.

    Grade:
    If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

    For DMVs below permissions are required as per Books On Line.

    To query a dynamic management view or function requires SELECT permission on object
    and VIEW SERVER STATE or VIEW DATABASE STATE permission. This lets you selectively
    restrict access of a user or login to dynamic management views and functions. To do
    this, first create the user in master and then deny the user SELECT permission on
    the dynamic management views or functions that you do not want them to access.
    After this, the user cannot select from these dynamic management views or functions,
    regardless of the database context of the user.


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/
    • Marked as answer bydb042188 Wednesday September 15, 2010 7:12 PM