- Auditing in 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 login auditing and control (Logon Trigger)
- Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable
- Using the standard SQL Server 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 in 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 over 70 security items to validate your database
- SQL Server - How to know the last login date of a user
- SQL Server - How to avoid and protect yourself from Ransomware attacks like WannaCry on your database server
- SQL Server - Watch out for the securityadmin server role! Using elevation of privileges to become sysadmin
- SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now.
- SQL Server - Understanding the risks of the TRUSTWORTHY property enabled on a database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change and Login Blocking after several Attempts
- SQL Server - How to create a login audit using instance logs
Hey guys!
Nesse post, eu gostaria de trazer uma solução para vocês de como saber a data do último login de um usuário no SQL Server, que é uma informação que frequentemente vejo alguém querendo saber como obter, especialmente para identificar os usuários que acessam o ambiente ou não, para fazer mapeamentos de dados e/ou excluir usuários que não estão acessando o banco mais.
Vou demonstrar algumas soluções que podem atender essa necessidade, mas nenhuma delas é algo nativo do SQL Server criado especificamente com esse intuito, como uma simples coluna de last_login numa view como a sys.server_principals, por exemplo.
Solução #1 – Coluna accdate na sys.syslogins (NÃO resolve)
|
1 2 |
select name, accdate from sys.syslogins |
“Solução” bem comum de encontrar em blogs e fóruns para resolver esse tipo de problema, essa consulta NÃO retorna a data do último logon de um usuário, como muitas pessoas acreditam. A view de catálogo sys.syslogins está marcada como “deprecated” desde o SQL Server 2005 e desde o SQL 2008, pelo menos, podemos identificar no código-fonte dessa view, que a coluna “accdate” tem sempre o mesmo valor da coluna “createdate”
Solução #2 – MAX(login_time) na sys.dm_exec_sessions (NÃO 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 |
Mais uma “solução” totalmente errada que eu vejo em muitos sites, blogs e fóruns, a utilização da DMV sys.dm_exec_sessions JAMAIS poderia ser usada para descobrir a data do último login de um usuário, pois essa DMV só mostra as informações das sessões ativas da instância.
Se a instância for reiniciada ou a sessão terminar de executar, por exemplo, essa informação já será perdida. Se quando a sua rotina de coleta dos dados de último login for executada e não tiver nenhuma sessão ativa mais para esse usuário, você também não terá essa informação disponível.
Por esses motivos, não considero essa consulta como uma solução para esse problema.
Solução #3 – Utilizando Login Auditing
Iniciando agora com soluções que de fato, funcionem, essa solução que vou apresentar consiste em ativar a auditoria de logins com sucesso também (o padrão é auditar somente falhas) e usar o errorlog para identificar as datas de logins dos usuários.
O ponto negativo dessa solução, é a quantidade de novos registros que vão passar a aparecer no errorlog. Além de consumir mais espaço, pode deixar mais difícil o troubleshooting de problemas utilizando o errorlog, devido à quantidade muito alta de linhas, dependendo da quantidade de logins da instância.
Como habilitar essa opção utilizando o SSMS:
Como habilitar essa opção utilizando o 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 utilizado para monitorar coletar os dados:
|
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:
Com esse script acima, basta você criar um job para executá-lo periodicamente e coletar os dados necessários. A tabela dbo.LastLogin terá os dados mais atuais sobre o último login de cada uusário.
Solução #4 – Utilizando uma trigger de logon
Uma outra forma de você conseguir criar esse histórico de logins, é utilizando triggers de logon para isso. Essa é uma solução onde você tem mais controle do que utilizando o recurso de Login Auditing, mas ao mesmo tempo, dá um pouco mais de trabalho para criar.
O ponto negativo dessa solução, é que assim como toda trigger de logon, caso você crie algo errado no desenvolvimento da trigger ou o usuário que for logar não tenha permissão na tabela de histórico, isso irá IMPEDIR que ele consiga logar no banco. Então tenha muito cuidado com isso, e teste bastante antes de aplicar em produção.
Criação das tabelas de histórico
|
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 |
Criação da 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 |
Gera a análise do último login de cada usuário
|
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:
Solução #5 – Utilizando Server Audit
Outra possível solução, é utilizar o recurso de Server Audit do SQL Server, o qual eu já demonstrei aqui no blog como utilizar através do artigo Auditing in SQL Server (Server Audit).
Nessa solução, que é bem leve do ponto de vista de consumo de recursos, eu vou criar um server audit e um server audit specification, e monitorar as ocorrências do evento SUCCESSFUL_LOGIN_GROUP. Irei inserir os dados retornados na tabela de histórico e depois analisar os resultados obtidos.
Criação das tabelas de histórico
|
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 |
Criação da 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 |
Criação da 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 |
Gera a análise do último login de cada usuário
|
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:
Bom pessoal, é isso! Espero que vocês tenham gostado dessas soluções que propus nesse artigo e que elas possam ajudar no seu dia a dia.
Tem outras formas de monitorar isso? Deixa aqui nos comentários
Um grande abraço e até mais!
Portuguese 
English