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.
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 community
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
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)
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)
Amount TrimmedUser 7 User1 3 User3 1 User2