- Audit on SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
- SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
- SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes to tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to Avoid Brute Force Attacks on Your Database
- SQL Server Security Checklist - An SP with more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
Speak guys!
In this post, I would like to bring you a solution on how to know the date of a user's last login to SQL Server, which is information that I often see someone wanting to know how to obtain, especially to identify the users who access the environment or not , to make data mappings and / or exclude users who are not accessing the bank anymore.
I will demonstrate some solutions that can meet this need, but none of them is something native to SQL Server created specifically for this purpose, such as a simple last_login column in a view like sys.server_principals, for example.
Solution # 1 - Accdate column in sys.syslogins (DOES NOT resolve)
|
1 2 |
select name, accdate from sys.syslogins |
Very common “solution” to find on blogs and forums to solve this type of problem, this query NO returns the date of a user's last login, as many people believe. The sys.syslogins catalog view is marked as “deprecated” since SQL Server 2005 and since SQL 2008, at least, we can identify in the source code of that view, that the “accdate” column always has the same value as the “ createdate ”
Solution # 2 - MAX (login_time) at sys.dm_exec_sessions (DOES NOT resolve)
|
1 2 3 4 5 6 7 |
SELECT login_name AS [Login], MAX(login_time) AS [Last Login Time] FROM sys.dm_exec_sessions GROUP BY login_name |
Another totally wrong “solution” that I see on many websites, blogs and forums, the use of the DMV sys.dm_exec_sessions could NEVER be used to find out the date of a user's last login, as this DMV only shows information from active sessions of the instance.
If the instance is restarted or the session ends running, for example, that information will already be lost. If when your last login data collection routine is performed and you have no more active sessions for that user, you will also not have this information available.
For these reasons, I do not consider this consultation as a solution to this problem.
Solution # 3 - Using Login Auditing
Starting now with solutions that actually work, this solution that I will present consists of enabling the audit of logins successfully also (the default is to audit only failures) and use the errorlog to identify the login dates of users.
The negative point of this solution is the number of new records that will appear in the errorlog. In addition to consuming more space, it can make troubleshooting more difficult using the errorlog, due to the very high number of lines, depending on the number of instance logins.
How to enable this option using SSMS:
How to enable this option using T-SQL:
|
1 2 3 4 5 6 |
EXEC sys.xp_instance_regwrite @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', @value_name = 'AuditLevel', @type = 'REG_DWORD', @value = 3 -- 0 = Nenhum / 1 = Apenas sucesso / 2 = Apenas falha / 3 = Sucesso e Falha |
Script used to monitor collecting data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
IF (OBJECT_ID('tempdb..#Arquivos_Log') IS NOT NULL) DROP TABLE #Arquivos_Log CREATE TABLE #Arquivos_Log ( [idLog] INT, [dtLog] NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AI, [tamanhoLog] INT ) IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados CREATE TABLE #Dados ( [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI, [User] AS (SUBSTRING(REPLACE([Text], 'Login succeeded for user ''', ''), 1, CHARINDEX('''', REPLACE([Text], 'Login succeeded for user ''', '')) - 1)) ) INSERT INTO #Arquivos_Log EXEC sys.sp_enumerrorlogs DECLARE @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log) WHILE(@Contador < @Total) BEGIN INSERT INTO #Dados (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login succeeded for user', NULL, NULL, NULL SET @Contador += 1 END IF (OBJECT_ID('tempdb..#UltimoLogin') IS NOT NULL) DROP TABLE #UltimoLogin CREATE TABLE #UltimoLogin ( [User] VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, LogDate DATETIME NOT NULL ) INSERT INTO #UltimoLogin SELECT [User], MAX(LogDate) AS LogDate FROM #Dados GROUP BY [User] -- Cria a tabela, se não existir IF (OBJECT_ID('dbo.LastLogin') IS NULL) BEGIN -- DROP TABLE dbo.LastLogin CREATE TABLE dbo.LastLogin ( Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, CreateDate DATETIME, LastLogin DATETIME NULL, DaysSinceLastLogin AS (DATEDIFF(DAY, ISNULL(LastLogin, CreateDate), CONVERT(DATE, GETDATE()))) ) END -- Insere os logins criados na instância INSERT INTO dbo.LastLogin (Username, CreateDate) SELECT [name], create_date FROM sys.server_principals A LEFT JOIN dbo.LastLogin B ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = B.Username WHERE is_fixed_role = 0 AND [name] NOT LIKE 'NT %' AND [name] NOT LIKE '##%' AND B.Username IS NULL AND A.[type] IN ('S', 'U') -- Atualiza a tabela de histórico com os dados atuais UPDATE A SET A.LastLogin = B.LogDate FROM dbo.LastLogin A JOIN #UltimoLogin B ON A.Username = B.[User] WHERE ISNULL(A.LastLogin, '1900-01-01') <> B.LogDate SELECT * FROM dbo.LastLogin |
Result:
With this script above, you just need to create a job to run it periodically and collect the necessary data. The dbo.LastLogin table will have the most current data on the last login of each user.
Solution # 4 - Using a logon trigger
Another way for you to create this logins history is to use logon triggers for this. This is a solution where you have more control than using the Login Auditing feature, but at the same time, it takes a little more work to create.
The negative point of this solution is that, like every logon trigger, if you create something wrong in the development of the trigger or the user who is going to log in does not have permission in the history table, this will PREVENT him from being able to log into the bank. So be very careful with this, and test a lot before applying in production.
Creation of history tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
IF (OBJECT_ID('dbo.LoginHistory') IS NULL) BEGIN -- DROP TABLE dbo.LoginHistory CREATE TABLE dbo.LoginHistory ( Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, LoginTime DATETIME NOT NULL, ProgramName VARCHAR(255) ) END IF (OBJECT_ID('dbo.LastLogin') IS NULL) BEGIN -- DROP TABLE dbo.LastLogin CREATE TABLE dbo.LastLogin ( Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, CreateDate DATETIME, LastLogin DATETIME NULL, DaysSinceLastLogin AS (DATEDIFF(DAY, ISNULL(LastLogin, CreateDate), CONVERT(DATE, GETDATE()))) ) END |
Creation of Trigger
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_LoginHistory') > 0) DROP TRIGGER [trgAudit_LoginHistory] ON ALL SERVER GO CREATE TRIGGER [trgAudit_LoginHistory] ON ALL SERVER -- Para evitar problemas de permissão no insert na tabela WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN SET NOCOUNT ON -- Não loga conexões de usuários de sistema IF (ORIGINAL_LOGIN() LIKE 'NT %' OR ORIGINAL_LOGIN() LIKE '##%' OR ORIGINAL_LOGIN() LIKE '%SQLServerAgent') RETURN -- Não loga conexões de softwares que ficam se conectando constantemente IF (PROGRAM_NAME() LIKE 'Red Gate%' OR PROGRAM_NAME() LIKE '%IntelliSense%' OR PROGRAM_NAME() LIKE 'SQLAgent %' OR PROGRAM_NAME() IN ('Microsoft SQL Server', 'RSPowerBI', 'RSManagement', 'TransactionManager', 'DWDiagnostics', 'Report Server')) RETURN INSERT INTO dbo.LoginHistory (Username, LoginTime, ProgramName) SELECT ORIGINAL_LOGIN(), GETDATE(), PROGRAM_NAME() END GO ENABLE TRIGGER [trgAudit_LoginHistory] ON ALL SERVER GO |
Generates the analysis of each user's last login
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
IF (OBJECT_ID('tempdb..#UltimoLogin') IS NOT NULL) DROP TABLE #UltimoLogin CREATE TABLE #UltimoLogin ( [User] VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, LogDate DATETIME NOT NULL ) INSERT INTO #UltimoLogin SELECT Username, MAX(LoginTime) AS LogDate FROM dbo.LoginHistory GROUP BY Username -- Insere os logins criados na instância INSERT INTO dbo.LastLogin (Username, CreateDate) SELECT [name], create_date FROM sys.server_principals A LEFT JOIN dbo.LastLogin B ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = B.Username WHERE is_fixed_role = 0 AND [name] NOT LIKE 'NT %' AND [name] NOT LIKE '##%' AND B.Username IS NULL AND A.[type] IN ('S', 'U') -- Atualiza a tabela de histórico com os dados atuais UPDATE A SET A.LastLogin = B.LogDate FROM dbo.LastLogin A JOIN #UltimoLogin B ON A.Username = B.[User] WHERE ISNULL(A.LastLogin, '1900-01-01') <> B.LogDate SELECT * FROM dbo.LastLogin |
Result:
Solution # 5 - Using Server Audit
Another possible solution is to use the Server Audit feature of SQL Server, which I already demonstrated here on the blog how to use it through the article Audit on SQL Server (Server Audit).
In this solution, which is very light from the point of view of resource consumption, I will create a server audit and a server audit specification, and monitor the occurrences of the SUCCESSFUL_LOGIN_GROUP event. I will insert the data returned in the history table and then analyze the results obtained.
Creation of history tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
IF (OBJECT_ID('dbo.LoginHistory') IS NULL) BEGIN -- DROP TABLE dbo.LoginHistory CREATE TABLE dbo.LoginHistory ( Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, LoginTime DATETIME NOT NULL, ProgramName VARCHAR(255) ) END IF (OBJECT_ID('dbo.LastLogin') IS NULL) BEGIN -- DROP TABLE dbo.LastLogin CREATE TABLE dbo.LastLogin ( Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, CreateDate DATETIME, LastLogin DATETIME NULL, DaysSinceLastLogin AS (DATEDIFF(DAY, ISNULL(LastLogin, CreateDate), CONVERT(DATE, GETDATE()))) ) END |
Creation of Server Audit
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_audits WHERE [name] = 'Auditoria_Arquivo') > 0) BEGIN ALTER SERVER AUDIT [Auditoria_Arquivo] WITH (STATE = OFF) DROP SERVER AUDIT [Auditoria_Arquivo] END CREATE SERVER AUDIT [Auditoria_Arquivo] TO FILE ( FILEPATH = N'C:\Auditoria\', -- Preste atenção no caminho do arquivo MAXSIZE = 20 MB, MAX_ROLLOVER_FILES = 4, RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = 'd5d61d0a-2173-4e97-8036-90586f79c6fc' ) ALTER SERVER AUDIT [Auditoria_Arquivo] WITH (STATE = ON) GO |
Creation of the Server Audit Specification
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_audit_specifications WHERE [name] = 'LoginAudit') > 0) BEGIN ALTER SERVER AUDIT SPECIFICATION [LoginAudit] WITH(STATE = OFF) DROP SERVER AUDIT SPECIFICATION [LoginAudit] END CREATE SERVER AUDIT SPECIFICATION [LoginAudit] FOR SERVER AUDIT [Auditoria_Arquivo] ADD (SUCCESSFUL_LOGIN_GROUP) WITH (STATE = ON) GO |
Generates the analysis of each user's last login
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
USE [master] GO DECLARE @UltimoRegistro DATETIME = DATEADD(SECOND, 1, ISNULL((SELECT MAX(LoginTime) FROM dbo.LoginHistory), '1900-01-01')) INSERT INTO dbo.LoginHistory ( Username, LoginTime, ProgramName ) SELECT DISTINCT server_principal_name, FORMAT(event_time, 'yyyy-MM-dd HH:mm:ss') AS LoginTime, application_name FROM sys.fn_get_audit_file('C:\Auditoria\*.sqlaudit',default,default) WHERE action_id = 'LGIS' AND event_time > @UltimoRegistro AND server_principal_name NOT LIKE 'NT %' AND server_principal_name NOT LIKE '##%' AND application_name NOT IN ('Microsoft SQL Server', 'RSPowerBI', 'RSManagement', 'TransactionManager', 'DWDiagnostics', 'Report Server') AND application_name NOT LIKE 'SQLAgent %' AND application_name NOT LIKE 'Red Gate%' AND application_name NOT LIKE '%IntelliS%' -- Executa a query para retornar as informações do último login IF (OBJECT_ID('tempdb..#UltimoLogin') IS NOT NULL) DROP TABLE #UltimoLogin CREATE TABLE #UltimoLogin ( [User] VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, LogDate DATETIME NOT NULL ) INSERT INTO #UltimoLogin SELECT Username, MAX(LoginTime) AS LogDate FROM dbo.LoginHistory GROUP BY Username -- Insere os logins criados na instância INSERT INTO dbo.LastLogin (Username, CreateDate) SELECT [name], create_date FROM sys.server_principals A LEFT JOIN dbo.LastLogin B ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = B.Username WHERE is_fixed_role = 0 AND [name] NOT LIKE 'NT %' AND [name] NOT LIKE '##%' AND B.Username IS NULL AND A.[type] IN ('S', 'U') -- Atualiza a tabela de histórico com os dados atuais UPDATE A SET A.LastLogin = B.LogDate FROM dbo.LastLogin A JOIN #UltimoLogin B ON A.Username = B.[User] WHERE ISNULL(A.LastLogin, '1900-01-01') <> B.LogDate SELECT * FROM dbo.LastLogin |
Result:
Well folks, that's it! I hope you enjoyed these solutions that I proposed in this article and that they can help in your day to day.
Are there other ways to monitor this? Leave it here in the comments
A big hug and even more!
