SQL Server - Cómo Conocer la Fecha del Último Inicio de Sesión de un Usuario

¡Hola, chicos!
En este post me gustaría traerles una solución sobre cómo saber la fecha del último inicio de sesión de un usuario en SQL Server, que es información que muchas veces veo que alguien quiere saber cómo obtener, especialmente para identificar usuarios que acceden o no al entorno, para realizar mapeos de datos y/o excluir usuarios que ya no acceden a la base de datos.

Demostraré algunas soluciones que pueden satisfacer esta necesidad, pero ninguna de ellas es algo nativo de SQL Server creado específicamente para este propósito, como una simple columna last_login en una vista como sys.server_principals, por ejemplo.

Quizás también te interese descubrir el fecha del último acceso a una tabla o vista y ejecución de un procedimiento en SQL Server.

Solución n.º 1: columna Accdate en sys.syslogins (NO se resuelve)

select name, accdate 
from sys.syslogins

Una “solución” muy común de encontrar en blogs y foros para solucionar este tipo de problemas, esta consulta NO devuelve la fecha del último inicio de sesión de un usuario, como mucha gente cree. La vista de catálogo sys.syslogins está marcada como “obsoleta” desde SQL Server 2005 y al menos desde SQL 2008, podemos identificar en el código fuente de esta vista que la columna “accdate” siempre tiene el mismo valor que la columna “createdate”

SELECT 
    login_name AS [Login], 
    MAX(login_time) AS [Last Login Time] 
FROM
    sys.dm_exec_sessions 
GROUP BY
    login_name

Otra “solución” totalmente incorrecta que veo en muchos sitios web, blogs y foros, usar el DMV sys.dm_exec_sessions NUNCA podría usarse para averiguar la fecha del último inicio de sesión de un usuario, ya que este DMV solo muestra información sobre las sesiones activas de la instancia.

Si se reinicia la instancia o la sesión termina de ejecutarse, por ejemplo, esta información ya se perderá. Si se ejecuta tu última rutina de recopilación de datos de inicio de sesión y no hay más sesiones activas para ese usuario, tampoco tendrás esta información disponible.

Por estos motivos, no considero que esta consulta sea una solución a este problema.

Comenzando ahora con soluciones que realmente funcionan, esta solución que voy a presentar consiste en activar también la auditoría de inicios de sesión exitosos (el valor predeterminado es auditar solo los fallos) y utilizar el registro de errores para identificar las fechas de inicio de sesión de los usuarios.

El punto negativo de esta solución es la cantidad de registros nuevos que aparecerán en el registro de errores. Además de consumir más espacio, puede dificultar la resolución de problemas utilizando el registro de errores, debido a la gran cantidad de líneas, dependiendo de la cantidad de inicios de sesión en la instancia.

Cómo habilitar esta opción usando SSMS:

Cómo habilitar esta opción usando 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 monitorear la recopilación de datos:

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:

Con este script anterior, simplemente crea un trabajo para ejecutarlo periódicamente y recopilar los datos necesarios. La tabla dbo.LastLogin tendrá los datos más recientes sobre el último inicio de sesión de cada usuario.

Solución n.º 4: utilizar un activador de inicio de sesión

Otra forma de crear este historial de inicio de sesión es utilizando activadores de inicio de sesión para ello. Esta es una solución en la que tienes más control que usando la función Auditoría de inicio de sesión, pero al mismo tiempo, requiere un poco más de trabajo para crearla.

El punto negativo de esta solución es que, como todo disparador de inicio de sesión, si creas algo mal en el desarrollo del disparador o el usuario que va a iniciar sesión no tiene permiso en la tabla del historial, esto IMPEDIRÁ que pueda iniciar sesión en la base de datos. Así que tenga mucho cuidado con esto y pruebe mucho antes de aplicarlo a producción.

Para obtener más información sobre los activadores de inicio de sesión, lea mi artículo. SQL Server – Cómo implementar auditoría y control de inicios de sesión (Logon Trigger).

Creando tablas de historial

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

Creación de disparador

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

Genera análisis del último inicio de sesión de cada usuario.

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:

Solución n.º 5: uso de la auditoría del servidor

Otra posible solución es utilizar la función Server Audit de SQL Server, que ya he demostrado aquí en el blog cómo utilizarla en el artículo. Auditoría en SQL Server (Server Audit).

En esta solución, que es muy liviana desde el punto de vista del consumo de recursos, crearé una auditoría del servidor y una especificación de auditoría del servidor, y monitorearé las ocurrencias del evento SUCCESSFUL_LOGIN_GROUP. Insertaré los datos devueltos en la tabla de historial y luego analizaré los resultados obtenidos.

Creando tablas de historial

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

Creación de auditoría de servidor

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

Creación de la especificación de auditoría del servidor

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

Genera análisis del último inicio de sesión de cada usuario.

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:

ATENCIÓN: La columna "nombre_aplicación" solo está disponible a partir de SQL Server 2017 en adelante. Si está utilizando una versión anterior, elimine esta columna del inserto en el código anterior.

Bueno amigos, ¡eso es todo! Espero que te hayan gustado las soluciones que propuse en este artículo y que puedan ayudarte en tu vida diaria.

¿Existen otras formas de monitorear esto? Déjalo aquí en los comentarios.
¡Un fuerte abrazo y hasta luego!

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…

Solución n.º 1: columna Accdate en sys.syslogins (NO se resuelve)

Solución #2 – MAX(login_time) en sys.dm_exec_sessions (NO resuelve)

Solución n.º 3: utilizar la auditoría de inicio de sesión

Solución n.º 4: utilizar un activador de inicio de sesión

Solución n.º 5: uso de la auditoría del servidor

Dirceu Resende

Comentários (0)