|
Step 1：Creating a Linked Server.
EXEC sp_addlinkedserver 'ADSI','Active Directory Services 2.5','ADSDSOObject','adsdatasource'
Step 2：Creating a SQL Server Authenticated Login
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ADSI',@locallogin = NULL,@useself = N'False',@rmtuser = N'domainAccount',@rmtpassword = N'Password'
对于 SQL Server 授权登录，可以使用sp_addlinkedsrvlogin 系统存储过程配置用于连接到目录服务的适当的登录/密码.
参考这里: http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
如果SQLServer使用Windows 授权登录，只需自映射就足以通过使用 SQL Server 安全委托来访问AD。简单点说就是直接运行第三步语句即可.
Step 3：Querying the Directory Service.
<div class="codetitle"><a style="CURSOR: pointer" data="11783" class="copybut" id="copybut11783" onclick="doCopy('code11783')"> 代码如下:<div class="codebody" id="code11783">
-- Query for a list of User entries in an OU using the SQL query dialect
select convert(varchar(50),[Name]) as FullName,
convert(varchar(50),Title) as Title,TelephoneNumber) as PhoneNumber
from openquery(ADSI,
'select Name,Title,TelephoneNumber
from ''LDAP://OU=Directors,OU=Atlanta,OU=Intellinet,DC=vizability,DC=intellinet,DC=com''
where objectClass = ''User''')
-- Query for a list of Group entries in an OU using the SQL query dialect
select convert(varchar(50),[Name]) as GroupName,[Description]) GroupDescription
from openquery(ADSI,Description
from ''LDAP://OU=VizAbility Groups,DC=com''
where objectClass = ''Group''')
引用：
http://msdn2.microsoft.com/en-us/library/aa772380.aspx
http://www.atlantamdf.com/presentations/AtlantaMDF_111201_examples.txt 说明：但是这样默认查询出来的是1000个对象.怎么办呢?
方法一,通过字母来循环.见以下:
<div class="codetitle"><a style="CURSOR: pointer" data="3498" class="copybut" id="copybut3498" onclick="doCopy('code3498')"> 代码如下:<div class="codebody" id="code3498">
CREATE TABLE #tmpADUsers
( employeeId varchar(10) NULL,
SAMAccountName varchar(255) NOT NULL,
email varchar(255) NULL)
GO
/*// AD is limited to send 1000 records in one batch. In an ADO interface you can define this batch size,not in OPENQUERY.
Because of this limitation,we just loop through the alphabet.
/
DECLARE @cmdstr varchar(255)
DECLARE @nAsciiValue smallint
DECLARE @sChar char(1)
SELECT @nAsciiValue = 65
WHILE @nAsciiValue < 91
BEGIN
SELECT @sChar= CHAR(@nAsciiValue)
EXEC master..xp_sprintf @cmdstr OUTPUT,'SELECT employeeId,SAMAccountName,Mail FROM OPENQUERY( ADSI,''SELECT Mail,employeeID FROM ''''LDAP://dc=central,dc=mydomain,dc=int''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s'''''' )',@sChar
INSERT #tmpADUsers
EXEC( @cmdstr )
SELECT @nAsciiValue = @nAsciiValue + 1
END
DROP TABLE #tmpADUsers
以上方法源自于：
我推荐的方法:在微软搜索到的. 如何通过 NTDSUtil为服务器修改限制 maxPageSize
|
