locked
Number of users accessing SQL Server 2012 RRS feed

  • Question

  • 1)find number of users on a database in sql server 2012
    2)find number of users accessing in sql server 2012
    3)find users using a specific object on a database inn sql server 2012
    4) active process in sql server

    Can someone provide me how to find above for SA on the server and a user who has db_reader role on server

    Thanks a lot in advance

    Wednesday, April 17, 2013 7:12 PM

Answers

  • After some modifications,hope the below code snippet will helps you! for your first 2 questions..

    SELECT

        DB_NAME(dbid) as DBName, 
        COUNT(dbid) as NumberOfConnections,
        loginame as LoginName
    FROM
        sys.sysprocesses
    WHERE 
        dbid > 0
    GROUP BY 
        dbid, loginame

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by SQListic Monday, April 22, 2013 6:26 PM
    Wednesday, April 17, 2013 7:47 PM
  • Hello,

    Count database user:
    SELECT count (mp.name) as database_user
    FROM sys.database_role_members drm
    JOIN  sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
    Users that are connected to the server:
    SELECT Count(login_name) as user_count
    FROM sys.dm_exec_sessions
    Active process:
    SELECT COUNT(session_id) AS session_count
    FROM sys.dm_exec_sessions
    WHERE  status='running'

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

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support


    • Edited by Fanny Liu Thursday, April 18, 2013 9:40 AM
    • Marked as answer by SQListic Monday, April 22, 2013 6:24 PM
    Thursday, April 18, 2013 9:34 AM

All replies

  • After some modifications,hope the below code snippet will helps you! for your first 2 questions..

    SELECT

        DB_NAME(dbid) as DBName, 
        COUNT(dbid) as NumberOfConnections,
        loginame as LoginName
    FROM
        sys.sysprocesses
    WHERE 
        dbid > 0
    GROUP BY 
        dbid, loginame

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by SQListic Monday, April 22, 2013 6:26 PM
    Wednesday, April 17, 2013 7:47 PM
  • Hello,

    Count database user:
    SELECT count (mp.name) as database_user
    FROM sys.database_role_members drm
    JOIN  sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
    Users that are connected to the server:
    SELECT Count(login_name) as user_count
    FROM sys.dm_exec_sessions
    Active process:
    SELECT COUNT(session_id) AS session_count
    FROM sys.dm_exec_sessions
    WHERE  status='running'

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

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support


    • Edited by Fanny Liu Thursday, April 18, 2013 9:40 AM
    • Marked as answer by SQListic Monday, April 22, 2013 6:24 PM
    Thursday, April 18, 2013 9:34 AM