How to track which users logged in to sql server

Hi All,
How to track which users logged in to sql server?
do they need to have only windows login to track them or sql logins to will work to track them?
saratcmAsked:
Who is Participating?
 
avalenzuelaCommented:
I got this from anotehr page...

select count(*) from master..sysprocesses where spid > 50

 will give you a rough number of  USERS.
- This will include logins from SQL Agent.
- Note that the same user can be using multiple connections from the same application,
   and thus be counted more than once.

0
 
avalenzuelaCommented:
use
   select * from sys.sysprocesses

it will list all process and what users are connected
0
 
saratcmAuthor Commented:
Thanks for the comments, will this give information only for the users who r currently logged in or also for  currently logged off of or users no longer in that session( disconnected)?
I mean in case if I want to find out which user logged in in last 30 min or so and they might have closed their session by the time I am executing this cmd
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
avalenzuelaCommented:
that will give you only active users
if you need history first you have to configure auditing..

The auditing information is written to the SQL Server error logs and to the Windows event log. To enable auditing of logins,
perform the following actions:
1) Open Enterprise Manager and connect to the database.
2) Click the right-mouse button on the instance and select Properties from the popup menu.
3) Open the 'Security' tab.
4) Under 'Audit Level' choose 'All'.
5) Click the OK button



then after restarting the SQL server
you can do a query like this...

EXEC sp_readerrorlog 0, 1, 'Login'

This procedure takes four parameters:

Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the results
0
 
Chris MangusDatabase AdministratorCommented:
I would use SQL Profiler and build traces that would track audit login and logoff events.

There is a great topic on this at http://msdn.microsoft.com/en-us/library/aa905160(SQL.80).aspx
0
 
saratcmAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.