Alrighty. We’re all data people here so you know we all agree that knowledge is power. One thing that is absolutely helpful in a large corporate environment is knowing if someone has been let go/laid off/fired. Using linked server to query active directory you can fairly easily (especially if someone else wrote the query) see whose accounts are disabled and which accounts have an expiration date. Obviously, these two things don’t guarantee someone is no longer with the company (or that their last day is coming up) but they can be pretty good indicators. The final result set on this query is named ‘The Goodbye List”.

Remember: this is sensitive information and while knowledge is power, with great power comes great responsibility.

IF OBJECT_ID('tempdb..#UserAccountControl') IS NOT NULL 
    BEGIN
        DROP TABLE #UserAccountControl
    END

CREATE TABLE #UserAccountControl
    (
      UserAccountControlValue INT
    , UserAccountControlDescription VARCHAR(1000)
    )

INSERT  #UserAccountControl
        ( UserAccountControlValue, UserAccountControlDescription )
VALUES  ( '512', 'Enabled Account' ),
        ( '514', 'Disabled Account' ),
        ( '544', 'Enabled, Password Not REQUIRED' ),
        ( '546', 'Disabled, Password Not REQUIRED' ),
        ( '66048', 'Enabled, Password Doesn''t Expire' ),
        ( '66050', 'Disabled, Password Doesn''t Expire' ),
        ( '66080', 'Enabled, Password Doesn''t Expire & Not Required' ),
        ( '66082', 'Disabled, Password Doesn''t Expire & Not Required' ),
        ( '262656', 'Enabled, Smartcard REQUIRED' ),
        ( '262658', 'Disabled, Smartcard Required' ),
        ( '262688', 'Enabled, Smartcard Required, Password Not REQUIRED' ),
        ( '262690', 'Disabled, Smartcard Required, Password Not Required' ),
        ( '328192', 'Enabled, Smartcard Required, Password Doesn''t Expire' ),
        ( '328194', 'Disabled, Smartcard Required, Password Doesn''t Expire' ),
        ( '328224',
          'Enabled, Smartcard Required, Password Doesn''t Expire & Not Required' ),
        ( '328226',
          'Disabled, Smartcard Required, Password Doesn''t Expire & Not Required' )

IF OBJECT_ID('tempdb..#tmpAD') IS NOT NULL 
    BEGIN
        DROP TABLE #tmpAD
    END

SELECT  samAccountName AS WinNT_ID
      , sn AS Last_Name
      , givenName AS First_Name
      , displayName AS Display_Name
      , ISNULL(title, 'N/A') AS Job_Title
      , ISNULL(department, 'N/A') AS Department
      , l AS City
      , userAccountControl
      , accountExpires
INTO    #tmpAD
FROM    OPENQUERY(YourActiveDirectoryLinkedServerName,
                  '
    SELECT 
		samAccountName
		, sn
		, givenName
		, title
		, displayName
		, department
		, mail
		, telephonenumber
		, mobile
		, facsimiletelephonenumber
		, physicalDeliveryOfficeName
		, l
		, streetAddress
		, st
		, postalCode
		, userAccountControl
		, accountExpires
	FROM	''LDAP:// DC=brandes,DC=com''
    WHERE	objectClass=''user''
		AND objectClass<>''computer''
    ') AS tblADSI
WHERE   sn IS NOT NULL

--the goodbye list
SELECT  CASE WHEN ad.accountexpires IN ( '9223372036854775807'
										, '0'
										, '129895740000000000' )
             THEN '9999-12-31'
             ELSE DATEADD(mi,
                          ( CAST(ad.accountExpires AS BIGINT) / 600000000 )
                          + DATEDIFF(Minute, GETUTCDATE(), GETDATE()),
                          CAST('1/1/1601' AS DATETIME2))
        END AS AccountExpiresDate
      , uac.UserAccountControlDescription
      , ad.Display_Name
      , ad.Department
      , ad.Job_Title
      , ad.WinNT_ID
      , ad.accountexpires
FROM    #tmpAD AS ad
        LEFT JOIN #UserAccountControl AS uac
            ON ad.useraccountcontrol = uac.UserAccountControlValue
WHERE   ad.WinNT_ID NOT LIKE '%[0-9]%' --eliminate temps by eliminating logins with a number in them
        AND ( CASE WHEN ad.accountexpires IN ( '9223372036854775807', '0',
                                               '129895740000000000' )
                   THEN '9999-12-31'
                   ELSE DATEADD(mi,
                                ( CAST(ad.accountExpires AS BIGINT)
                                  / 600000000 ) + DATEDIFF(Minute,
                                                           GETUTCDATE(),
                                                           GETDATE()),
                                CAST('1/1/1601' AS DATETIME2))
              END <= GETDATE() + 14
              OR UserAccountControlDescription LIKE '%disabled%'
            )
ORDER BY 1 DESC