Solved

How to track which users logged in to sql server

Posted on 2010-09-07
6
759 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

831 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