Consult last access date Login MS SQL Server

3

To know the last access of a Login to a database in SQL Server; Perform the following query to obtain dates however the LOGINPROPERTY function does not contain the last login date of Login.

    SELECT 
     dbp.name AS [nombreUsuarioBD],
     slg.name AS [nombreLogin],
     LOGINPROPERTY ( slg.name , 'PasswordLastSetTime') AS 
     [ultimoCambioContraseña] ,
     LOGINPROPERTY ( slg.name , 'LockoutTime') AS [fechaDeBloqueo],
     LOGINPROPERTY ( slg.name , 'BadPasswordTime') AS 
     [ultimoIntentoContraseñaIncorrecta]
FROM sys.sql_logins slg
INNER JOIN 
sys.database_principals dbp
ON slg.sid = dbp.sid
WHERE slg.type IN ('U','S')

and consult the table by consulting the "accdate" column, however, it does not include exact information.

 SELECT 
  name, 
  accdate
 FROM  
 sys.syslogins
    
asked by Cristian Zauco 14.09.2017 в 15:11
source

1 answer

4

It would only be necessary to consult sight SYS.DM_EXEC_SESSIONS .

SELECT MAX(LOGIN_TIME) [ULTIMA FECHA]
   ,LOGIN_NAME [CUENTA]
FROM SYS.DM_EXEC_SESSIONS
GROUP BY LOGIN_NAME;

Reference:

answered by 14.09.2017 / 15:28
source