T-SQL: Find last logon dates

You may use the T-SQL code below to find the last logon dates for the SQL accounts.

use master;
set nocount on
-- find users, last logon time, and disabled state
declare @users_login_time   table
    [type]          varchar(255)
,   [login_name]        varchar(255)
,   [last_logon_time]   datetime
,   [disabled]      varchar(255)
insert into @users_login_time
    'type'          = [type_desc]
,   'login_name'        = sdes.[login_name]
,   'last_logon_time'   = max(sdes.[login_time])
,   'disabled'      = case when ssprin.[is_disabled] = 1 then cast('Disabled' as varchar) else '' end
    sys.dm_exec_sessions sdes join sys.server_principals ssprin on sdes.[login_name] = ssprin.[name]
group by
,   is_disabled
,   [type_desc]
select * from @users_login_time
-- create list of all known domain users from last logon
declare @domain_accounts    table ([accounts]   varchar(255))
insert into @domain_accounts 
select [login_name] from @users_login_time where [login_name] like '%\%'
select * from @domain_accounts
-- get domain accounts and their associated groups
declare @get_group_members  varchar(max)
set @get_group_members  = ''
select  @get_group_members  = @get_group_members + 
'exec master..xp_logininfo ''' + [accounts] + ''', ''all'';' + char(10)
from    @domain_accounts
declare @groups table
    [account_name]      varchar(255)
,   [type]          varchar(255)
,   [privilege]     varchar(255)
,   [mapped_login_name] varchar(255)
,   [permission_path]   varchar(255)
insert into @groups exec (@get_group_members)
select * from @groups
-- find accounts and their associated groups
    'group_name'        = case when [permission_path] is null then 'No Group - Individually Added' else [permission_path] end
,   'account_name'      = [account_name]
order by
,   [account_name]
Share your love