Post reply

User access to Activity Monitor

  • December 8, 2011 at 3:26 am

    #391070

    Hi All,

    We have a few 'superusers' who need the ability from time to time to see who's connected to their database.

    I do not want to GRANT VIEW SERVER STATE ON 'login' as this seems overkill for what they need.

    The tsql below will give them exactly what they want. But when either user executes the sp, it only returns their connection info. I presume additional permissions are required to read other users metadata?

    CREATE PROCEDURE [dbo].[Get_ActivityMonitor_Info]

    AS

    SELECT spid, status, LogiName, HostName, Blocked, db_name(dbid) AS 'DatabaseName', CMD

    FROM master..sysprocesses

    WHERE db_name(dbid) = 'db_name'

    At the moment they have select permissions on the sys.sysprocesses table and obviously execute permissions on the sp.

    I'm sure there's better ways of going about this based on least privilege. I would be grateful for your ideas.

  • December 8, 2011 at 5:40 am

    #1418620

    Try context switching...

    Using EXECUTE AS in Modules

    http://msdn.microsoft.com/en-us/library/ms178106.aspx

  • December 8, 2011 at 6:20 am

    #1418638

    Thanks Dev.

    I was looking into this when you replied. Any idea what permissions I would need to grant to allow a login to see other user processes in Activity monitor?

  • December 8, 2011 at 6:27 am

    #1418643

    Shabba (12/8/2011)

    Thanks Dev.

    I was looking into this when you replied. Any idea what permissions I would need to grant to allow a login to see other user processes in Activity monitor?

    You don't need to provide any extra privilege to user who is going to execute this procedure. EXECUTE permission on procedure is sufficient. But the procedure should run in the User's context that has sufficient permissions. It's safe to use sa or equivalent user's context here because it's scope is very limited i.e. within procedure with limited query.

  • December 8, 2011 at 9:42 am

    #1418783

    Shabba (12/8/2011)

    Thanks Dev.

    I was looking into this when you replied. Any idea what permissions I would need to grant to allow a login to see other user processes in Activity monitor?

    To allow the Activity Monitor UI to function GRANT VIEW SERVER STATE to the login that wants to launch it.

    For the homegrown proc solution you have looked into it requires instance-level permissions which means EXECUTE AS comes with lots of baggage when it comes to impersonation. Instead of EXECUTE AS I would recommend signing the proc with a certificate and granting EXEC to whoever needs the proc.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • December 8, 2011 at 11:34 am

    #1418868

    You give this script to them to execute and get the detailed infio they required

    http://qa.sqlservercentral.com/scripts/Head+Blocker/75366/

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • December 9, 2011 at 8:13 am

    #1419403

    Thanks everyone for your advice.

Viewing 7 posts - 1 through 6 (of 6 total)

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