locked
How to view all active connections on a single database with a normal, non-SA login? RRS feed

  • Question

  • Hello, everyone.

    I am writing a simple SP to run in the context of a normal sql login, that must detect any active connections to a particular database, before starting to run.

    I found the obvious sp_who and also a simpler query that, for now, has been working in my login (sa, of course):

    SELECTdb_name(dbid) as DBName, count(dbid) as NumberConns, loginame as Login
    FROM sys.sysprocesses
    WHERE dbid > 0 and DB_NAME(dbid) = 'Sample_Database'
    GROUP BY dbid, loginame

    This query returns active connections and their number for each login. Works fine with SA. Now I need to run this or something like in the context of a normal login, and it does not work. The code only sees, of course, its own connection..

    Any ideas?

    Thanks in advance,

    Ivo Pereira

    IT Consultant

    Portugal


    ComputerDoc
    Friday, April 30, 2010 1:30 PM

Answers

  • Wrap that command in a stored procedure with the EXECUTE AS directive, grant your user access to the stored procedure.
    • Proposed as answer by Tom Li - MSFT Monday, May 3, 2010 5:43 AM
    • Marked as answer by Tom Li - MSFT Thursday, May 6, 2010 11:06 AM
    Friday, April 30, 2010 3:31 PM
  • You have to grant your login with view server state permission, so that they can query all the system connections. But this permission allows them to view other dvms and dmfs.

    grant view server state to login_name

    To be able to run Server-scoped dynamic management views and functions it requires VIEW SERVER STATE permission on the server.

     


    Thanks, Leks
    • Proposed as answer by Tom Li - MSFT Monday, May 3, 2010 5:43 AM
    • Marked as answer by Tom Li - MSFT Thursday, May 6, 2010 11:06 AM
    Sunday, May 2, 2010 6:49 AM

All replies

  • Wrap that command in a stored procedure with the EXECUTE AS directive, grant your user access to the stored procedure.
    • Proposed as answer by Tom Li - MSFT Monday, May 3, 2010 5:43 AM
    • Marked as answer by Tom Li - MSFT Thursday, May 6, 2010 11:06 AM
    Friday, April 30, 2010 3:31 PM
  • You have to grant your login with view server state permission, so that they can query all the system connections. But this permission allows them to view other dvms and dmfs.

    grant view server state to login_name

    To be able to run Server-scoped dynamic management views and functions it requires VIEW SERVER STATE permission on the server.

     


    Thanks, Leks
    • Proposed as answer by Tom Li - MSFT Monday, May 3, 2010 5:43 AM
    • Marked as answer by Tom Li - MSFT Thursday, May 6, 2010 11:06 AM
    Sunday, May 2, 2010 6:49 AM