locked
How many time user was login RRS feed

  • Question

  • H iteam

    we have abc user 11.07.2012 how many time he connected to database i want logon history details of that user.

    Tx


    subu

    Monday, July 30, 2012 3:04 PM

Answers

  • Hi Subu999,

    Thank you for your update.  To enable login Audit in SQL Server you can follow  Basit mentioned.
    For more information, please refer to How to: Configure Login Auditing (SQL Server Management Studio)

    Alternatively you can try to use DMV  sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. So if you have had the session open for quite some time and ran multiple queries it gave the sum of all those.

    For more information, please refer to sys.dm_exec_sessions (Transact-SQL)


    Regards, Amber zhang

    • Marked as answer by amber zhang Monday, August 6, 2012 2:02 AM
    Tuesday, July 31, 2012 2:22 AM
  • hi

    There are three ways to find who is connected to a database in sqlserver. 

    First one:

    • Use the SQL SERVER Management Studio -- Management -- Activity Monitor
    • This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

     Second One:

    • Use the built in stored procedure called sp_who2
    • Run the command exec sp_who2
    • This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.

    Third One:

    • Use the script

    select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'databasename'


    Ahsan Kabir

    • Marked as answer by amber zhang Monday, August 6, 2012 2:02 AM
    Friday, August 3, 2012 5:19 AM

All replies

  • Well you can view the  user logon history from error log only if Logon auditing is enabled. To enable logon auditing right-click SQL Server in Object Explorer and choose properties and then activate security tab and then choose option Both failed and successful logins as follow:

    Alternatively create logon trigger on the server.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    • Proposed as answer by Basit Farooq Monday, July 30, 2012 4:10 PM
    Monday, July 30, 2012 4:10 PM
  • Hi Farooq,

    How can we Logon auditing is enabled ? guide  me here in future purpose we have to follow. the same, In Activity monitor how many days history we have to see?

    Tx


    subu

    Monday, July 30, 2012 4:46 PM
  • Hi Subu999,

    Thank you for your update.  To enable login Audit in SQL Server you can follow  Basit mentioned.
    For more information, please refer to How to: Configure Login Auditing (SQL Server Management Studio)

    Alternatively you can try to use DMV  sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. So if you have had the session open for quite some time and ran multiple queries it gave the sum of all those.

    For more information, please refer to sys.dm_exec_sessions (Transact-SQL)


    Regards, Amber zhang

    • Marked as answer by amber zhang Monday, August 6, 2012 2:02 AM
    Tuesday, July 31, 2012 2:22 AM
  • Hi,

       You can query out the error log using xp_readerrorlog and filter the errorlog by passing parameter to see howmany times he has logged in.

       Or

       Alternatively you can dump the errorlog in a temporary table and query the table to see how many times an user "abc" logged in.

    Thanks

    Raf

    Thursday, August 2, 2012 4:50 AM
  • hi

    There are three ways to find who is connected to a database in sqlserver. 

    First one:

    • Use the SQL SERVER Management Studio -- Management -- Activity Monitor
    • This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

     Second One:

    • Use the built in stored procedure called sp_who2
    • Run the command exec sp_who2
    • This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.

    Third One:

    • Use the script

    select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'databasename'


    Ahsan Kabir

    • Marked as answer by amber zhang Monday, August 6, 2012 2:02 AM
    Friday, August 3, 2012 5:19 AM