sql - SQL LDAP 查询问题
问题描述
最近受命查看此问题,但无法弄清楚为什么它不起作用。这是在我之前由其他人创建的,我不是经验丰富的 dba。无论如何,问题是我们的联系人目录中没有更新一些用户的详细信息,而其他的则根本不存在。我做了一些挖掘,发现了一个使用 ldap 查询来命中 AD 并将用户拉回来的 SP。这些用户都存在于 AD 中,并且看不到他们没有通过的任何其他问题。proc 语法的一部分是使用 ADSI 作为链接服务器的 openquery,我知道它的 1000 行限制。我将在下面发布proc代码,如果有人能看到任何明显的东西,请赐教。
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
解决方案
经过进一步调查,我们发现这是一个单独的进程/作业导致了问题。
推荐阅读
- excel - 是否有在 excel vba 消息框中使用 vba 语法来强制用户单击“确定”或关闭 excel?
- android - 如何从笑脸评级栏获取当前选择?
- c - 如何使用 MinGW 使用 ssl 编译 libcurl
- php - 我怎样才能让这个 Php WP_Query 只返回一次类别?
- r - 忽略不符合条件的值,按条件按组密集排名或编号
- html - 仅在 Internet Explorer 上输入表单输入键不会触发提交
- javascript - AWS S3 使用预签名 URL 进行身份验证的用户访问?
- php - 如何使用 ODBC 方法将 PHP 连接到 DBF 文件
- firebase - 在不启用 App Engine 的情况下使用 Firestore
- sql - 执行联接后从 SQL Server 中的 XML 中提取信息