Post reply

View Server State Permission question regarding sp_who2 and security issues/holes

  • September 19, 2018 at 11:22 am

    #379194

    We are running SQL Server 2012 SP3 on Windows 2008 Server. Are there any security issues/holes with granting the View Server State Permission to developers? The sp_who2 stored procedure only returns current session details. They want to be able to see all executing sessions on the instance of SQL Server. Would this permission also allow them to see the Activity Monitor? (I don't believe this permission allows them to kill sessions.) 

    Thanks for any suggestions/help.

  • September 19, 2018 at 11:43 am

    #2006319

    this  is a great example of testing it yourself, using EXECUTE AS can let you truly visualize and understand permissions for a user

    say mydomain\lowell is the guy you want to be able to use sp_who2, but we are not sure if the permission makes a difference:

    this code block adds the permission, tests sp_who2 both before and after so you can see what that person would see:


    EXECUTE AS LOGIN='mydomain\lowell'
    SELECT SUSER_NAME()
    EXECUTE sp_who2
    REVERT -- turn back into my sysadmin
    GRANT VIEW SERVER STATE TO [mydomain\lowell]
    EXECUTE AS LOGIN='mydomain\lowell'
    SELECT SUSER_NAME()
    EXECUTE sp_who2
    REVERT -- turn back into my sysadmin
    REVOKE VIEW SERVER STATE TO [mydomain\lowell]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 19, 2018 at 12:53 pm

    #2006330

    HookSqlDba7 - Wednesday, September 19, 2018 11:22 AM

    We are running SQL Server 2012 SP3 on Windows 2008 Server. Are there any security issues/holes with granting the View Server State Permission to developers? The sp_who2 stored procedure only returns current session details. They want to be able to see all executing sessions on the instance of SQL Server. Would this permission also allow them to see the Activity Monitor? (I don't believe this permission allows them to kill sessions.) 

    Thanks for any suggestions/help.

    No they can't kill sessions with view server state. In terms of activity monitor, they can view most of it but additional permissions are needed for view the data file I/O:
    Open Activity Monitor (SQL Server Management Studio)

    There usually is some level of risk when granting additional privileges. With view server state, they can see some details, parameters for SQL statements so if you have sensitive data there could be issues and it likely isn't a good idea to grant that permission.

    Sue

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

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