none
SP_WHO2 on SQL 2005

    Question

  • The behavior seems to have changed on SP_WHO2 in SQL 2005. 

    In SQL 2000 I could be logged in as a regular user in my system and run SP_WHO2 and get all the users currently logged in.  This is no longer working in SQL 2005.  It now only returns "me".

    What kind of rights do you need to have to see who is logged in?

    Thursday, March 16, 2006 2:18 PM

Answers

  • It looks like sp_who2 is not documented yet in Books Online. I'll open a bug to address this.

    I expect that sp_who2 has similar permission requirements with sp_who.

    http://msdn2.microsoft.com/en-US/library/ms174313.aspx

    The user must have VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server. Otherwise, the user sees only the current session.

    So, VIEW SERVER STATE is the permission that you need to see everyone.

    Also, see the page on sysprocesses, on top of which both sp_who and sp_who2 are implemented:

    http://msdn2.microsoft.com/en-us/library/ms179881.aspx

    Thanks
    Laurentiu

    Thursday, March 16, 2006 6:44 PM
    Moderator

All replies

  • It looks like sp_who2 is not documented yet in Books Online. I'll open a bug to address this.

    I expect that sp_who2 has similar permission requirements with sp_who.

    http://msdn2.microsoft.com/en-US/library/ms174313.aspx

    The user must have VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server. Otherwise, the user sees only the current session.

    So, VIEW SERVER STATE is the permission that you need to see everyone.

    Also, see the page on sysprocesses, on top of which both sp_who and sp_who2 are implemented:

    http://msdn2.microsoft.com/en-us/library/ms179881.aspx

    Thanks
    Laurentiu

    Thursday, March 16, 2006 6:44 PM
    Moderator
  • i was wondering if it is safe to grant these permissions to a regular user?

    Anything i should be cautious about?

    thanks

    Friday, May 26, 2006 4:02 PM
  • VIEW SERVER STATE allows its grantees access to a lot of information in the server. Rather than granting the permission to a regular user, consider wrapping access to specific resources into a signed stored procedure and associate the permission with the certificate used to sign such stored procedures.

    For another alternative and additional information on this permission, see http://msdn2.microsoft.com/en-us/library/ms188754.aspx.

    Thanks
    Laurentiu

    Friday, May 26, 2006 9:19 PM
    Moderator