Post reply

Sessions History for each SQL

  • October 9, 2007 at 7:44 am

    #118954

    I am trying to complete a central table in a specific server to monitoring the concurrent logins on every SQL Network. The steps:

    1- I created a Table in the CentralServer

    2- I created a LinkServer for each SQL I want know their connections

    3- A new sqluser called LINKMON is created on this SQL with DATAREADER permission on MASTER DB (For execute Select on SYS.dm_exec_sessions)

    The problem consist in each select of this new user return only his connection, so what kind of specific permission must have LINKMON to can list all sessions??(Obviously that dont must be a hight level user)

    SP_HELPTEXT 'sys.dm_exec_sessions' :

    CREATE VIEW sys.dm_exec_sessions AS

    SELECT * FROM OpenRowset(TABLE SYSSESSIONS)

  • October 9, 2007 at 7:46 am

    #740357

    If it is SQL 2005, may be you should use DDL triggers/alerts, to be NOTIFIED about logins, intead of constantly monitoring them (with a probability of losing a short-lived login occured before monitoring ticks)

  • October 10, 2007 at 4:10 am

    #740684

    Hi

    sys.dm_exec_sessions requires VIEW SERVER STATE permission on server.

    "Keep Trying"

  • October 10, 2007 at 6:51 am

    #740763

    Great!!! It worked!

    🙂

    Thanks!!!!

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

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