Configuration on Server
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'[10.10.10.10]'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\user',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
') AS tblADSI
ORDER BY SAMAccountName
select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName,displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND sAMAccountName = ''username''
') AS tblADSI
ORDER BY displayname
Find groups and users all together
-- Configure AD Domain to search
DECLARE @strDC NVARCHAR(100)
SET @strDC = 'dc=csodmgmt,dc=corp'
-- Declare Variables
DECLARE @chvAlphaChars VARCHAR(60),
@chvSearch VARCHAR(10),
@chvSearchLevel1 VARCHAR(1),
@chvSearchLevel2 VARCHAR(1),
@chvSearchLevel3 VARCHAR(1),
@intcountLevel1 INT,
@intcountLevel2 INT,
@intcountLevel3 INT,
@intRowCount INT,
@strSQL NVARCHAR(4000),
@strADSISQL NVARCHAR(4000),
@Login VARBINARY(85),
@CN VARCHAR(512),
@CT INT,
@ADRoot VARCHAR(255)
-- Declare temp tables
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Users'))
DROP TABLE #LDAP_AD_V2_USERS
CREATE TABLE [DBO].[#LDAP_AD_V2_USERS] (
[ROW_ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[SID] [VARBINARY](85) NULL,
[SAMACCOUNTNAME] [NVARCHAR](256) NULL,
[CN] [NVARCHAR](256) NULL,
[SN] [NVARCHAR](256) NULL,
[DISPLAYNAME] [NVARCHAR](256) NULL,
[GIVENNAME] [NVARCHAR](256) NULL,
[TELEPHONENUMBER] [NVARCHAR](256) NULL,
[ADSPATH] [NVARCHAR](256) NULL,
[HOMEDIRECTORY] [NVARCHAR](256) NULL,
[MAIL] [NVARCHAR](256) NULL,
[MEMBEROF] [NVARCHAR](256) NULL,
[PRIMARYGROUPID] [INT],
[CREATETIMESTAMP] DATETIME)
ON [PRIMARY]
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Groups'))
DROP TABLE #LDAP_AD_V2_GROUPS
CREATE TABLE [DBO].[#LDAP_AD_V2_GROUPS] (
[ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[LOGIN] [VARCHAR](512) NULL,
[EMAIL] [VARCHAR](255) NULL,
[ACCTNAME] [VARCHAR](512) NULL,
[DISTNAME] [VARCHAR](512) NULL,
[CREATEDDATE] [DATETIME] NULL,
[CHANGEDDATE] [DATETIME] NULL,
[MGR] [VARCHAR](512) NULL,
[SID] [VARBINARY](85) NULL)
ON [PRIMARY]
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_UserGroups'))
DROP TABLE #LDAP_AD_V2_USERGROUPS
CREATE TABLE [DBO].[#LDAP_AD_V2_USERGROUPS] (
[USERLOGIN] [VARBINARY](85) NOT NULL,
[GROUPLOGIN] [VARBINARY](85) NULL)
ON [PRIMARY]
-- POPULATION OF USERS
-- Search letters to cycle through
-- any chars, but the first char must be a space
SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ.-_`0123456789'
SET NOCOUNT ON
-- start on non space char
SET @intcountLevel1 = 2
-- first level loop
WHILE @intcountLevel1 <= LEN(@chvAlphaChars)
BEGIN
-- get first level char
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
-- reset start on space
SET @intcountLevel2 = 1
-- second level loop
WHILE @intcountLevel2 <= LEN(@chvAlphaChars)
BEGIN
-- reset start on space
SET @intcountLevel3 = 1
-- third level loop
WHILE @intcountLevel3 <= LEN(@chvAlphaChars)
BEGIN
-- setup the string to search for. By using the trim function we can form each level depending on no records
-- eg A 99, B 1000 > BA 9, BB 20 etc
-- trim the spaces forming just A, B, C ; AA, AB for search etc
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))
SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3
SET @strADSISQL = 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'where objectCategory = ''''Person'''' '
+ CHAR(13) + 'and objectClass = ''''user'''' '
+ CHAR(13) + 'and sAMAccountName = ''''' + @chvSearch + '*'''' '
SET @strSQL = 'insert into #LDAP_AD_V2_Users (sid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp) '
+ CHAR(13) + 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'
EXEC SP_EXECUTESQL @strSQL
SET @intRowCount = @@ROWCOUNT
-- prints what string is being searched for : no of inserts
-- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)
-- if searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B
IF @intRowCount < 1000
AND @chvSearchLevel2 = ''
SET @intcountLevel2 = @intcountLevel2 + 100
-- if searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB
IF @intRowCount < 1000
AND @chvSearchLevel3 = ''
SET @intcountLevel3 = @intcountLevel3 + 100
-- else over 1000 so increment third level
SET @intcountLevel3 = @intcountLevel3 + 1
END
-- increment next second level char
SET @intcountLevel2 = @intcountLevel2 + 1
END
-- increment next first level char
SET @intcountLevel1 = @intcountLevel1 + 1
END
-- POPULATION OF GROUPS
SET @strADSISQL = 'select objectSid, managedBy, whenChanged, whenCreated, distinguishedName, name, samAccountName, mail '
+ CHAR(13) + 'FROM ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'WHERE objectCategory = ''''Group'''' '
SET @strSQL = 'insert into #LDAP_AD_V2_Groups (sid,mgr,changeddate,createddate,distname,acctname,email,login) '
+ CHAR(13) + 'select objectSid,managedBy,whenChanged,whenCreated,distinguishedName,name,mail,samAccountName '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'
-- PRINT @strSQL
EXEC SP_EXECUTESQL @strSQL
-- POPULATION OF USER > GROUPS RELATIONSHIP TABLE
IF EXISTS (SELECT *
FROM TEMPDB.DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('tempdb.dbo.#CT'))
DROP TABLE #CT
-- Create a temporary table to hold AD user SID values
CREATE TABLE #CT (CT VARBINARY(85))
-- Declare and open a cursor to step through the list of Active Directory groups
DECLARE CURGROUPS CURSOR FOR
SELECT SID,
DISTNAME
FROM #LDAP_AD_V2_GROUPS
ORDER BY LOGIN
OPEN CURGROUPS
FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN
WHILE @@FETCH_STATUS = 0
BEGIN
-- Empty the temp table
TRUNCATE TABLE #CT
-- Build a SQL statement to insert the SID values directly from the linked server into the temp table
SET @strsql = 'INSERT #CT
SELECT *
FROM OPENQUERY (
ADSI,
''SELECT objectSid
FROM ''''LDAP://' + @strDC + '''''
WHERE objectCategory = ''''User''''
AND memberof=''''' + REPLACE(@CN,'''','''''''''') + ''''''')'
EXEC( @strsql)
-- Select the number of records inserted. If this value is less than 1000 then there is no need
-- to execute the OLE calls, and we simply copy the values into the correlation table.
SELECT @CT = COUNT(* )
FROM #CT
--PRINT @ct
--PRINT @strsql
IF @CT <> 0
BEGIN
INSERT #LDAP_AD_V2_USERGROUPS
(USERLOGIN,
GROUPLOGIN)
SELECT CT,
@Login
FROM #CT
WHERE CT IS NOT NULL
END
FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN
END
CURSORERROR:
CLOSE CURGROUPS
DEALLOCATE CURGROUPS
DROP TABLE #CT
/*
-- Individual SQL Statements to see users, groups & relationships
select * from #LDAP_AD_V2_Users
select * from #LDAP_AD_V2_Groups
select * from #LDAP_AD_V2_UserGroups
*/
-- Final Query to tie together the 3 tables and produce a report of group membership
SELECT #LDAP_AD_V2_GROUPS.ACCTNAME AS GROUPNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME AS USERACCOUNT
FROM #LDAP_AD_V2_USERGROUPS
INNER JOIN #LDAP_AD_V2_USERS
ON #LDAP_AD_V2_USERGROUPS.USERLOGIN = #LDAP_AD_V2_USERS.SID
INNER JOIN #LDAP_AD_V2_GROUPS
ON #LDAP_AD_V2_USERGROUPS.GROUPLOGIN = #LDAP_AD_V2_GROUPS.SID
where #LDAP_AD_V2_USERS.SAMACCOUNTNAME = '
username '
ORDER BY #LDAP_AD_V2_GROUPS.ACCTNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME
