SQL Server - Como saber a data do último login de um usuário

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.

Você também pode se interessar por descobrir a data do último acesso a uma tabela ou view e execução da uma procedure no SQL Server.

Solução #1 – Coluna accdate na sys.syslogins (NÃO resolve)

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”

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.

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:

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:

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.

Para saber mais sobre triggers de logon, dê uma lida no meu artigo SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon).

Criação das tabelas de histórico

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

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

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

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

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

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

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:

ATENÇÃO: A coluna “application_name” está disponível apenas a partir do SQL Server 2017. Caso você esteja utilizando uma versão anterior, remova essa coluna do insert no código anterior.

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!

Dirceu Resende

Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Veja minhas certificações

Comentários (0)

Carregando comentários…

Solução #1 – Coluna accdate na sys.syslogins (NÃO resolve)

Solução #2 – MAX(login_time) na sys.dm_exec_sessions (NÃO resolve)

Solução #3 – Utilizando Login Auditing

Solução #4 – Utilizando uma trigger de logon

Solução #5 – Utilizando Server Audit

Dirceu Resende

Posts relacionados

Comentários (0)