Query Active Directory to Display Login Information

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, --Name of the linked server for Active directory
'
SELECT
displayName
,sAMAccountName
,sn
,givenName
,extensionAttribute6
,department
,badPwdCount
,userAccountControl
FROM
''LDAP://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.' ;

/*2011-05-19 Line breaks added by junk.jjk*/

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, --Name of the linked server for Active directory
'SELECT displayName, sAMAccountName, sn, givenName, extensionAttribute6, department, badPwdCount, userAccountControl
FROM ''LDAP://DC=us,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.';

William Soranno (5/19/2011)

Rudy,
You should edit you post so the code is multiple lines. One long line is diffucult to read.
Here is a multi line version.

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, --Name of the linked server for Active directory
              '
SELECT
    displayName
   ,sAMAccountName
   ,sn
   ,givenName
   ,extensionAttribute6
   ,department
   ,badPwdCount
   ,userAccountControl
FROM
    ''LDAP://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.' ;