Alexandre,
Isso mesmo, através de um Linked Server apontando para o AD do Controlador de Domínio é possível obter estes dados, veja o exemplo:
--cria o linkedserver ADSI
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
--script para listar os usuários
select *
FROM OPENQUERY( ADSI,
'SELECT name, userAccountControl, sAMAccountName, givenname, sn, mail, telephoneNumber, title, department, physicalDeliveryOfficeName, company, l, st, postalcode, extensionAttribute1
FROM ''GC://corp.contoso.com.br''
WHERE objectCategory = ''Person'' AND objectClass = ''User''')
exec sp_addlinkedserver
@server='SERVERWINDB',
@srvproduct='Active Directory Services 2.5',
@provider='ADsDSOObject',
@datasrc='SERVERWINDB'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'SERVER',
@useself = false,
@locallogin = 'Junior',
@rmtuser ='CN=Administrador,CN=Users,DC=LS-AD,DC=LSR,DC=COM,DC=BR',
@rmtpassword = '123456'
create view v_UsuarioAD
as
SELECT *
FROM OPENQUERY(MSAD,
'SELECT distinguishedName, sAMAccountName, userPrincipalName,
givenName, sn, telephoneNumber, l, st, userAccountControl
FROM ''LDAP://SERVER/DC=LS-AD,DC=LSR,DC=com,DC=br''
WHERE objectClass = ''user''')
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]