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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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.
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:
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
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
sys.sysprocesses
is deprecated and is only provided for backwards compatibility and should no longer be used (see note in article you referenced).
Jul 23, 2018 at 8:21