I was the original poster of this thread and with help from others here, I have had continued success. I have had success with querying AD on SQL 2000 Standard, all the way up to 2008 R2 32/64bit (with the same linked server config). I find there is a 2000 record limit so I cannot get all of the AD accounts from Exchange in a single result set. Here is an outline of what I do. Some overhead, but I have not gone back and optimized. Hopefully it may help someone out. *You may need to fill in some blanks in code. Also, truncate tables for a reload.
---TABLES
CREATE TABLE [dbo].[tblActiveDirectory](
[ActiveDirectoryId] [smallint] IDENTITY(1,1) NOT NULL,
[Dn] [varchar](200) NOT NULL,
[RecType] [varchar](1) NULL,
[DisplayName] [varchar](100) NULL,
[Sam] [varchar](75) NULL,
[Mail] [varchar](150) NULL,
[Department] [varchar](150) NULL)
CREATE TABLE [dbo].[tblActiveDirectoryMembers](
[DnChild] [varchar](200) NOT NULL,
[DnParent] [varchar](200) NOT NULL)
SPROCS:
-----Insert OU's:
-----spAdInsertOus
-- run first
INSERT INTO dbo.tblActiveDirectory
(
DN,
RecType,
DisplayName
)
SELECT DistinguishedName,
'O',
Name
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT name, displayName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''organizationalUnit'' ')
-----Insert Groups:
-----spInsertGroups
-- run 2nd
INSERT INTO dbo.tblActiveDirectory
(
DN,
RecType,
DisplayName,
)
SELECT DistinguishedName,
'G',
Name,
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT Name, DistinguishedName, Mail
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''Group'' ')
--------Insert Users (this is not optimized but it avoids the 1000/2000k limit on my system)
-- you may need to delete records from these results
--------spAdInsertusers
-- run 3rd
INSERT INTO dbo.tblActiveDirectory
(
Dn,
RecType,
DisplayName,
SAM,
)
SELECT DistinguishedName, 'U' [RecType], DisplayName, SamAccountName, mail
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''User''
AND objectCategory = ''Person''
AND userAccountControl <> ''514''
AND displayName < ''h''')
UNION ALL
SELECT distinguishedName, 'U' [RecType], displayName, samAccountName, mail
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''User''
AND objectCategory = ''Person''
AND userAccountControl <> ''514''
AND displayName >= ''h''
AND displayName <= ''p''')
UNION ALL
SELECT distinguishedName, 'U' [RecType], displayName, samAccountName, mail
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''User''
AND objectCategory = ''Person''
AND userAccountControl <> ''514''
AND displayName >= ''p''
AND displayName <= ''z''')
----- Insert Members (for associations between ou's, groups and users):
----- spAdInsertMembers
-- run 4th
DECLARE @csr cursor,
@DN varchar(500),
@sql nvarchar(max)
--------------------------------------------------------------------------
-- Set Cursor for DN of Organizational Units "O"
--------------------------------------------------------------------------
SET @csr = CURSOR STATIC FOR
SELECT Dn
FROM dbo.tblActiveDirectory
WHERE RecType IN ('O', 'G')
AND Dn NOT LIKE '%''%'
OPEN @csr
FETCH NEXT
FROM @csr
INTO @DN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = "
SELECT distinguishedName, '" + @DN + "'
FROM OPENQUERY(ADSI,
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://corp.mydomain.com/DC=corp,DC=mydomain,DC=com''
WHERE memberOf = ''" + @DN + "''
AND userAccountControl <> 514
AND objectClass = ''User''
AND objectCategory = ''Person'' ')
UNION ALL
SELECT distinguishedName, '" + @DN + "'
FROM OPENQUERY(ADSI,
'SELECT mail, Name, distinguishedName
FROM ''LDAP://corp.mydomain.com/DC=corp,DC=mydomain,DC=com''
WHERE memberOf = ''" + @DN + "''
AND userAccountControl <> 514
AND objectClass = ''Group'' ')"
INSERT INTO dbo.tblActiveDirectoryMembers
(
DnChild,
DnParent
)
EXEC sp_executesql @sql
FETCH NEXT
FROM @csr
INTO @DN
END
CLOSE @csr
DEALLOCATE @csr
---- sample view
CREATE VIEW [dbo].[vwAdGroupsByUser]
AS
WITH cteAdGroupMembership
(DisplayName, Sam, Mail, RecType, GroupName, GroupSam, GroupMail, GroupRecType)
AS
(SELECT u.DisplayName, u.Sam, u.Mail, u.RecType,
SELECT DisplayName, Sam, Mail, RecType, GroupName, GroupSam, GroupMail, GroupRecType
FROM cteAdGroupMembership AS cteAdGroupMembership_1
---- query your view for group members
SELECT *
FROM vwAdGroupsByUser
WHERE groupname = 'MyDepartment'