0

I need some help on how to find all the users that are connected to one of my database. I should check this on weekends to find out all the users that connected during the week.

Thank you.

2
  • You can check through the DMV like (select * from master.sys.dm_exec_connections where session_id>=51;) Commented Jul 23, 2018 at 8:00
  • 1. Why do you think that by checking who is connected on weekends you'll "find out all the users that connected during the week"? 2. One connects to server, not to database. If I do "use master; select * from db_1.dbo.tbl1 join db_2.dbo.tbl2" to which database I'm connecting? To master, to db_1, to db_2 or to 3 databases?
    – sepupic
    Commented Jul 23, 2018 at 8:52

2 Answers 2

2

You can check connected logins with the following query:

SELECT
    sdes.session_id,
    sdes.login_time,
    sdes.last_request_start_time,
    sdes.last_request_end_time,
    sdes.is_user_process,
    sdes.host_name,
    sdes.program_name,
    sdes.login_name,
    sdes.status,
    sdec.num_reads,
    sdec.num_writes,
    sdec.last_read,
    sdec.last_write,
    sdes.reads,
    sdes.logical_reads,
    sdes.writes,
    sdes.client_interface_name,
    sdes.nt_domain,
    sdes.nt_user_name,
    sdec.client_net_address,
    sdec.local_net_address
FROM
    sys.dm_exec_sessions AS sdes
    INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id

However, this query will only retrieve currenty connected sessions. if you would like to know which logins were logged into the server, you need to set up a trace, enable the audit for error log or use extended events.

Here's a solution to enable audit for error log:

  1. Using SSMS, right click your server and go to Properties.
  2. On Security, below Login auditing click either "Successful logins only" or "Both failed and successful logins", depending if you also want failures or not.
  3. Restart the SQL Server service (unfortunately).
  4. Information will be stored on the Error Log. Data will be deleted over a time so make sure to review it's retention policy. To read the information on the error log you can use sys.sp_readerrorlog. The first parameter is the error log number (0 = current, 1 = first archive, etc.), second parameter determines the source to read (1 for error log) and the third parameter is the literal you want to filter info from:

    EXEC sys.sp_readerrorlog 0, 1, 'login'
    

    A more visual alternative to read the log is going to Management (on SSMS), then SQL Server Logs and double click on any log.


To see amount of logins by time you can use the following script (I'm using the "failed login" event because it's the one my server is tracking):

IF OBJECT_ID('tempdb..#FailedLogins') IS NOT NULL
    DROP TABLE #FailedLogins

CREATE TABLE #FailedLogins (
    LogDate DATETIME,
    ProcessInfo VARCHAR(100),
    Message VARCHAR(MAX))

INSERT INTO #FailedLogins (
    LogDate,
    ProcessInfo,
    Message)
EXEC sys.sp_readerrorlog 0, 1, 'login failed'


SELECT TOP 5 * FROM #FailedLogins AS F

Result:

LogDate                 ProcessInfo Message
2018-07-16 09:47:52.240 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-16 12:31:51.930 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-17 13:38:05.520 Logon       Login failed for user 'User2'. Reason: Could not ...
2018-07-17 13:42:14.130 Logon       Login failed for user 'User3'. Reason: Password ...
2018-07-17 13:42:28.690 Logon       Login failed for user 'User3'. Reason: Password ...

How to obtain user from these messages (this will highly depend on the message being shown on the error log):

DECLARE @Message VARCHAR(MAX) = 'Login failed for user ''User1''. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]'

SELECT
    Original = @Message,
    UserStartPoint = SUBSTRING(@Message, 24, 100),
    TrimmedUser = LEFT(
        SUBSTRING(@Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(@Message, 24, 100)) - 1)

Result:

Original: Login failed for user 'User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]  
UserStartPoint: User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]   
TrimmedUser: User1

Group by and count between a period:

DECLARE @PeriodStartDate DATE = '2018-07-16'
DECLARE @PeriodEndDate DATE = GETDATE()

SELECT
    Amount = COUNT(1),
    TrimmedUser = LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)
FROM
    #FailedLogins AS F
WHERE
    F.LogDate BETWEEN @PeriodStartDate AND @PeriodEndDate
GROUP BY
    LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)

Result:

Amount  TrimmedUser
7       User1
3       User3
1       User2
2
  • is there a way to add a count column to see how many times a user connected through the week? or month?
    – NULL.Dude
    Commented Jul 23, 2018 at 12:51
  • 1
    @NULL.Dude i've added an example of how to group between a period.
    – EzLo
    Commented Jul 23, 2018 at 13:50
-1

To get information about users that are currently connected to the database you can use the sp_who procedure.

Alternatively you could use sys.sysprocesses as described in this answer

1
  • 3
    sys.sysprocesses is deprecated and is only provided for backwards compatibility and should no longer be used (see note in article you referenced).
    – John K. N.
    Commented Jul 23, 2018 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.