Query Active Directory to Display Login Information

,

Change the word MYDOMAIN to your network's domain name before executing.

PRINT 'Active Directory Query Script'
PRINT ''
PRINT '... Creating link conenction to Active Directory'
GO
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI')
EXEC master.dbo.sp_dropserver @server=N'ADSI', @droplogins='droplogins'
GO
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
GO
PRINT '... Execute SQL query against Active Directory'
GO
IF OBJECT_ID('tempdb..#ADList')IS NOT NULL
BEGIN
  SELECT '... Dropping temporary table' 
  DROP TABLE #ADList
END
PRINT '... Collecting data from Active Directory and inserting into temp table'
GO
  SELECT * INTO #ADList FROM OpenQuery
  (ADSI,'SELECT displayName,
    sAMAccountName,
    sn,
    givenName, 
    extensionAttribute6,  
    department, 
    badPwdCount, 
    userAccountControl
  FROM ''LDAP://OU=Staff,DC=MYDOMAIN,DC=COM'' where objectClass = ''User''')
  GO
  PRINT ' ... Displaying data from temp table'
  GO
  SELECT [displayName] AS 'Display Name',  
  [sn] AS 'Surname',  
  [givenName] AS 'Given Name',  
  [sAMAccountName] AS 'Account Name',  
  [extensionAttribute6] AS 'Computer Name', 
  'bad-Pwd' = (CASE WHEN [badPwdCount] = 17 THEN 'Entered Bad Password' ELSE badPwdCount END),  
  'AcctCtrl' = (CASE WHEN [userAccountControl] = 2 THEN 'Account is Disabled' 
  WHEN userAccountControl = 16 THEN 'Account Locked Out' 
  WHEN userAccountControl = 17 THEN CONVERT (VARCHAR(48),'Entered Bad Password') 
  WHEN userAccountControl = 32 THEN CONVERT (VARCHAR(48),'No Password is Required') 
  WHEN userAccountControl = 64 THEN CONVERT (VARCHAR(48),'Password CANNOT Change') 
  WHEN userAccountControl = 512 THEN 'Normal' WHEN userAccountControl = 514 THEN 'Disabled Account' 
  WHEN userAccountControl = 8192 THEN 'Server Trusted Account for Delegation' 
  WHEN userAccountControl = 524288 THEN 'Trusted Account for Delegation' 
  WHEN userAccountControl = 590336 THEN 'Enabled, User Cannot Change Password, Password Never Expires' 
  WHEN userAccountControl = 65536 THEN CONVERT (VARCHAR(48),'Account will Never Expire')  
  WHEN userAccountControl = 66048 THEN 'Enabled and Does NOT expire Paswword' 
  WHEN userAccountControl = 66050 THEN 'Normal Account, Password will not expire and Currently Disabled' 
  WHEN userAccountControl = 66064 THEN 'Account Enabled, Password does not expire, currently Locked out' 
  WHEN userAccountControl = 8388608 THEN CONVERT (VARCHAR(48),'Password has Expired')  ELSE CONVERT (VARCHAR(248),userAccountControl) END) 
  FROM #ADList 
  WHERE [givenName] IS NOT NULL 
  ORDER BY [sn] ASC
  GO
  PRINT '... Removing temp table'
  GO
  DROP TABLE #ADList
  GO
  PRINT '... Removing link conenction to Active Directory'
  GO
  IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI')
  EXEC master.dbo.sp_dropserver @server=N'ADSI', @droplogins='droplogins'
  GO
  PRINT ' AD Data Collection Complete.';

Rate

2 (13)

Share

Share

Rate

2 (13)