- Auditoria no SQL Server (Server Audit)
- Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
- Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
- SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
- Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
- Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
- SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
- SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
- SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
- SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
- SQL Server – Trigger para prevenir e impedir alterações em tabelas
- SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
- SQL Server – Como evitar ataques de força bruta no seu banco de dados
- SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
- SQL Server – Como saber a data do último login de um usuário
- SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
- SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
- SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
- SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
- SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
- SQL Server – Como criar uma auditoria de logins utilizando os logs da instância
Fala pessoal!
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 |
Resultado:
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 |
Resultado:
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 Auditoria no 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 |
Resultado:
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