SQL Server – Audit Users Connected to the Database

auditconnectivitysql server

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.

Best Answer

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
Related Question