Post reply

sp_who2 permissions

  • August 6, 2018 at 2:45 am

    #407622

    Good Morning Experts,
    A user wants to be able to run sp_who2 and submitted an approved ticket . I gave the user below permission:
    GRANT VIEW SERVER STATE TO user.
    However, user is reporting that he is able to see only 1 row when he executes sp_who2. Could you please let me know what is needed so that the user can see full output of sp_who2.

  • August 6, 2018 at 5:47 am

    #2000393

    According to the documentation, sp_who (Transact-SQL) - Permissions (yes, i realise that's sp_who, not sp_who2, but they need the same), you only need VIEW SERVER STATE. If you don't have that permission, then the login only sees details for the current session (which would likely only be one row).

    This implies either you didn't give the Login (not user, a user and login are not the same thing) the VIEW SERVER STATE permission, or they have an explicit DENY somewhere. I tested this, and VIEW SERVER STATE does work:

    USE master;
    GO

    CREATE LOGIN TestLogin WITH PASSWORD = 'abc123', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
    GO
    EXECUTE AS LOGIN = 'TestLogin';
    --Returns 1 row
    EXEC sp_who2;
    REVERT;

    GO
    GRANT VIEW SERVER STATE TO TestLogin;
    GO
    EXECUTE AS LOGIN = 'TestLogin';
    --Returns Many rows
    EXEC sp_who2;
    REVERT;
    GO
    DROP LOGIN TestLogin;
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply