T-SQL Query Active Directory

print 'Active Directory Query Script';
print '';
print '... Creating link conenction to Active Directory';
go

if exists(select srv.name from sys.servers as srv where srv.server_id != 0 and srv.name = N'ADSI')
    begin
        exec master.dbo.sp_dropserver @server = N'ADSI', @droplogins = 'droplogins'
    end;
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 as srv where srv.server_id != 0 and srv.name = N'ADSI')
    begin
        exec master.dbo.sp_dropserver @server = N'ADSI', @droplogins = 'droplogins'
    end;
go

print ' AD Data Collection Complete.';