sql ldap查询问题

nle07wnf  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(408)

最近接到任务来研究这个问题,但无法理解为什么它不起作用。这是别人在我之前创建的,我不是一个uber经验丰富的dba。无论如何,问题是,一些用户的详细信息没有得到更新,在我们的联系目录和其他根本不存在。我做了一些挖掘,发现了一个sp,它使用ldap查询来点击ad并将用户拉回来。这些用户都存在于广告中,看不到他们为什么不能通过的任何其他问题。proc语法的一部分是openquery,它使用adsi作为链接服务器,我知道这有1000行的限制。我将张贴下面的程序代码,如果有人能看到任何明显的,请启发我。

USE [Web_Repository]
GO
/******Object:  StoredProcedure [dbo].[get_activedirectory]    Script Date: 08/03/2020 11:04:57******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[get_activedirectory]
as
--GET ACTIVE DIRECTORY USERS OF ENABLED ACOUNTS
declare @i integer, @accname varchar(1000), @sql varchar(8000)

if exists (select * from sysobjects where name = 'activedirectory_temp' ) Drop Table activedirectory_temp

CREATE TABLE [activedirectory_temp] (
    [sn] [varchar] (256)   NULL ,
    [GivenName] [varchar] (256)   NULL ,
    [displayName] varchar (256) NULL,
    [Department] [varchar] (256)   NULL ,
    [Title] [varchar] (256)   NULL ,
    [SAMAccountName] [varchar] (256)   NULL ,
    [Company] [varchar] (256)   NULL ,
    [mail] [varchar] (256)   NULL ,
    [physicalDeliveryOfficeName] [varchar] (256)   NULL ,
    [enabled] bit NULL, 
    [wWWHomePage] [varchar] (256) NULL
) ON [PRIMARY]

set @sql = 'insert into dbname.dbo.activedirectory_temp select top 901 sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName,0 enabled, wWWHomePage from openquery (ADSI,''SELECT sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName, wWWHomePage FROM ''''LDAP://DC=Group,DC=Net'''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND (UserAccountControl = ''''512'''' OR UserAccountControl = ''''640'''' or company = ''''zeroc'''' or SAMAccountName = ''''137499'''' or SAMAccountName = ''''157067'''' or SAMAccountName = ''''WebIE10test'''') ORDER BY SAMAccountName'')'
set @sql = replace(@sql,'dbname',db_name())
exec(@sql)

set @i = @@rowcount

while @i <> 0
begin
    set @accname = (select max(SAMAccountName) from activedirectory_temp)
    --OU=Group Less Restricted
    set @sql = 'insert into dbname.dbo.activedirectory_temp select top 901 sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName,0 enabled, wWWHomePage from openquery (ADSI,''SELECT sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName, wWWHomePage FROM ''''LDAP://DC=Group,DC=Net'''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND (UserAccountControl = ''''512'''' OR UserAccountControl = ''''640'''' or company = ''''zeroc'''' or SAMAccountName = ''''137499'''' or SAMAccountName = ''''157067'''' or SAMAccountName = ''''WebIE10test'''') AND SAMAccountName > ''''####'''' ORDER BY SAMAccountName'')'
    set @sql = replace(@sql,'dbname',db_name())
    set @sql = replace(@sql,'####',@accname)
    exec(@sql)

    set @i = @@rowcount
end

--REMOVE ALL ADMIN & DUMMY ACCOUNTS
update activedirectory_temp
set enabled = 1
where sn > ''
and isnull(givenname,'') > ''
and isnull(title,'') not in ('Resource','Additional')
and isnull(sn,'') <> 'Template'
--and mail is not null
--and left(mail,1) <> '_'

delete from activedirectory_temp
where enabled <> 1

--LOAD DATA INTO LIVE TABLE IF SUCCESSFULL
set @i = (select count(*) from activedirectory_temp)

/*  MN 23/08/2016
    Insert changed to a select distinct to remove duplicate problem for sort order difference between LDAP and SQL
    ie SQL orders local variable @accname (SAMAccountName) natpaint -> N-E-H, whilst LDAP orders N-E-H -> natpaint 
    This causes a duplicate when selecting 901 rows into the temp table  */

if @i > 0
begin
    truncate table activedirectory

    insert into activedirectory (sn,GivenName,displayName,Department,Title,SAMAccountName,Company,mail,physicalDeliveryOfficeName, wWWHomePage)
    select distinct sn,GivenName,displayName,Department,Title,SAMAccountName,Company,mail,physicalDeliveryOfficeName, wWWHomePage
    from activedirectory_temp
end

if exists (select * from sysobjects where name = 'activedirectory_temp' ) Drop Table activedirectory_temp
gzszwxb4

gzszwxb41#

经过进一步调查,我们发现是一个单独的过程/工作导致了问题。

相关问题