Trond Hindenes教给我们一个很方便的方法把保存在AD中的OU用户信息当作数据库视图使用,你可以很方便的使用OU信息,比如建立企业通讯录、开发和其他系统帐号同步程序等。那么让我们仔细看看实现的途径吧:
首先,你需要在数据库中建立一个链接服务器,打开SQL Server Enterprise Manager Console,展开Security节点,然后是Linked Servers,右键New Linked Server…。在Linked Server文本框中输入一个ADSI,Provider name选择OLE DB Provider for Microsoft Directory Services,OK,关闭对话框。
然后建立一个新数据库也叫ADSI吧。建立一个View,其内容是:
SELECT
sAMAccountname AS AccountName,
GivenName AS [ First Name ] ,
SN AS [ Last Name ] ,
mail AS [ E-mail Address ] ,
TelephoneNumber AS [ Telephone Number ] ,
l AS Office,
PostalAddress AS [ Postal Address ] ,
PostalCode,
StreetAddress AS [ Street Address ] ,
wWWHomePage AS [ Web Page ] ,
distinguishedname AS DN
FROM
OPENQUERY (
ADSI,
' SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage FROM '' LDAP://portalcn.com/ OU=Norway ,DC=portalcn,DC=com '' WHERE objectCategory = '' Person '' AND objectClass = '' user '' ORDER BY Name '
) Rowset_1
sAMAccountname AS AccountName,
GivenName AS [ First Name ] ,
SN AS [ Last Name ] ,
mail AS [ E-mail Address ] ,
TelephoneNumber AS [ Telephone Number ] ,
l AS Office,
PostalAddress AS [ Postal Address ] ,
PostalCode,
StreetAddress AS [ Street Address ] ,
wWWHomePage AS [ Web Page ] ,
distinguishedname AS DN
FROM
OPENQUERY (
ADSI,
' SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage FROM '' LDAP://portalcn.com/ OU=Norway ,DC=portalcn,DC=com '' WHERE objectCategory = '' Person '' AND objectClass = '' user '' ORDER BY Name '
) Rowset_1
其中要修改几个位置,LDAP://AD名称/ OU=OU名称, DC=AD名称, DC=com。如果OU是层级结构的,并且希望只取某个子层次中的用户,则可以用OU=上级OU名称, OU=OU名称,以此类推。
一切就是这么简单,后面的工作就不叙述了,基于数据库视图的编程应该很简单了。