SET UP a Linked server with name ADSI to make this work (shown below)
![clip_image002[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5BCJNtdJpPOtaT3j3uNq0SxJSgIKPXlUPzioJYU5Z2qcpqvd16rDAnuOtkYzTlVg2hgO971o6Ov5tqOVyih5y2evHfKEdBoROFS13VLk-rwgzO60RKpYyaf2TnqM_AFOR_IBXSwBhdDU/?imgmax=800 "clip_image002[4]")
![clip_image002[6]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6UEbuI-jQIRT5RDMbkMTMZzEy6obNQxxH8YBcL9uYebussOVBPLbCsJG6yysLbaLaRB75FBvix8m7dNLXo4IeXVGaB_2vzr5t8uJi1VLFoespSzUwrb5zatqq1WQvqGjVTuLN9SOQEa0/?imgmax=800 "clip_image002[6]")
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
![clip_image002[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh0r6g6hcGu-UUWYqC-nr4V9I_eWgCFvdwqrtDGtp5dDMIjhPTovAV0NKnoeX9HVAlZTkiVoj-IrV5m_vumsWvXQzbApA0PEFqKIQLr1WgqBAaFcI66G-pgIvbwQ9hICtZW6cXhJI52kw/s1600-h/clip_image002%25255B4%25255D%25255B2%25255D.jpg "clip_image002[4]")
![clip_image002[6]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIIsYFtdhyphenhyphennzVjZrhcfksaQ166sZvKI1534dP8GhV6Z55PJTUfzeeBT7l7bYTpF5QKx_pmikWRoQOrLV1VV6p-jOtxQIPHA9CBDU-6LNf_VaRrC8BkeLKKm4m3VEAAjIasNV-s8YL3tws/s1600-h/clip_image002%25255B6%25255D%25255B2%25255D.jpg "clip_image002[6]")
