SQL server 2008 r2 Last database user date and time

Question

How to find last database users date and time sql server 2008 r2?

Answer 1

You can try this:

SELECT accdate, name FROM master.dbo.syslogins

or use

select max (login_time), login_name 
from sys.dm_exec_sessions
group by login_name;

Answer 2

You could set up a so-called logon trigger.

Example:

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

^{Example taken from https://msdn.microsoft.com/en-us/library/bb326598(v=sql.105).aspx}