How to query Active Directory from SQL server

SET UP a Linked server with name ADSI to make this work (shown below)

Also further on you'll see the use of the userAccountControl field, which is a flags field that stores user account details. The list of which flags are which can be found here...

http://msdn.microsoft.com/en-us/library/windows/desktop/ms680832(v=vs.85).aspx

 

use [utilities]
SET NOCOUNT ON;

-- Ensure database link to AD called ADSI exists...

IF object_id('[Utilities].[dbo].[AccountSyncCheck]') IS NOT NULL DROP TABLE [dbo].[AccountSyncCheck]
CREATE TABLE [dbo].[AccountSyncCheck](
      [LOGINID] [varchar](100) collate database_default NOT NULL,
      [EMAILADDRESS] [varchar](255) collate database_default NULL,
      [EMPLOYEEID] [varchar](10) collate database_default NULL,
      [FIRSTNAME] [varchar](255) collate database_default NULL,
      [LASTNAME] [varchar](255) collate database_default NULL,
      [DEPARTMENT] [varchar](255) collate database_default NULL,
      [OFFICENAME] [varchar](255) collate database_default NULL,
      [DISPLAYNAME] [varchar](255) collate database_default NULL,
      userAccountControl      int,
CONSTRAINT [PK_AccountSyncCheck_LOGINID] PRIMARY KEY CLUSTERED
(
      [LOGINID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

IF object_id('tempdb..#temp') IS NOT NULL  DROP TABLE #temp;
CREATE TABLE #Temp
(
    samaccountname             VARCHAR (100) ,
    givenname                  VARCHAR (255)  NULL,
    sn                         VARCHAR (255)  NULL,
    mail                       VARCHAR (255)  NULL,
    department                 VARCHAR (255)  NULL,
    physicalDeliveryOfficeName NVARCHAR (255) NULL,
    employeeid                 VARCHAR (15)   NULL,
    displayname                VARCHAR (255)  NULL,
    userAccountControl              int null
);

DECLARE @sql      AS VARCHAR (MAX)
DECLARE @i        AS INT = ASCII('A');

WHILE @i <= ASCII('Z')
    BEGIN
        SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                    FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                          FROM ''''LDAP://dc=mydomain,dc=com'''' 
                            WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                            AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                            AND         samaccountname >= '''' ' + char(@i) + '  ''''  and  samaccountname < '''' ' + char(@i + 1) + ' ''''  
                            '') ';
PRINT @SQL
        INSERT INTO #Temp
        EXECUTE (@sql);
        SET @i = @i + 1;
    END

-- deal with oddball characters
SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname ,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname < ''''A''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname >= ''''ZZZZZZZ''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

IF (SELECT COUNT(*)
    FROM   #Temp) > 0
    BEGIN
        truncate table AccountSyncCheck
        INSERT INTO AccountSyncCheck (LOGINID,EMAILADDRESS,EMPLOYEEID,FIRSTNAME,LASTNAME,DEPARTMENT,OFFICENAME,DISPLAYNAME, userAccountControl)
        SELECT   samaccountname,
                 mail,
                 LEFT(employeeid, 10),
                 givenname,
                 sn,
                 department,
                 physicalDeliveryOfficeName,
                 displayname,
                 userAccountControl
        FROM     #Temp
        ORDER BY samaccountname;
    END

SET NOCOUNT OFF;

--select top 10 * from beacon.dbo.rraperson
select *
from utilities.dbo.AccountSyncCheck

/*
SELECT     
'active RRA person(s) with invalid email address' AS     alert,
iv.PersonID, InternetAddress, Preferred, OfficeID, Loginname, Active, Responsibility, RetiredDate
from beacon.dbo.IV_PersonInternetAddressCompletePreferred iv
inner join beacon.dbo.rraperson r on r.personid = iv.personid
where ISNULL(iv.internetaddress, '') <> ''
--and iv.internetaddress not like '%@mydomain.com%' and iv.internetaddress not like '%@russreyn.com%'
and active = 1
and iv.personid not in (10000165,40002053,900008913,40001263,40001285)
*/

-- Active in Beacon disabled in AD
select *
from utilities.dbo.AccountSyncCheck

-- find active where they should be inactive
select *
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydomain\' + b.loginid 
where r.Active = 1
and useraccountcontrol & 2 = 2

-- find beacon spelling mistakes in email addresses
select b.loginid, pcp.PersonID, ActiveDirectoryEmailAddress = b.emailaddress, BeaconEmailAddress = pcp.InternetAddress
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydonamin\' + b.loginid 
inner join Beacon.dbo.IV_PersonInternetAddressCompletePreferred pcp on pcp.PersonID = r.id
where pcp.InternetAddress <> b.emailaddress
and r.Active = 1
and useraccountcontrol & 2 = 0