Solved

How to track which users logged in to sql server

Posted on 2010-09-07
6
763 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:saratcm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:avalenzuela
ID: 33622242
use
   select * from sys.sysprocesses

it will list all process and what users are connected
0
 
LVL 3

Accepted Solution

by:
avalenzuela earned 500 total points
ID: 33622263
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
 

Author Comment

by:saratcm
ID: 33622377
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 3

Assisted Solution

by:avalenzuela
avalenzuela earned 500 total points
ID: 33622585
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
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33623170
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
 

Author Closing Comment

by:saratcm
ID: 33662851
Thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question