Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to track which users logged in to sql server

Posted on 2010-09-07
6
Medium Priority
?
770 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 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 3

Assisted Solution

by:avalenzuela
avalenzuela earned 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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