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.' ;