Solved

How to track which users logged in to sql server

Posted on 2010-09-07
6
761 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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