Ever wondered how you could publish your Active Directory in a SharePoint List. It will show all entries dynamically.
First of all you need to setup a linked server in MS SQL using the ADSL provider
-- CREATE LINKED Server to ADSI -- sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource' -- CREATE VIEW in the ICT DB USE ICT ; GO IF OBJECT_ID ('ADSI_USERS', 'V') IS NOT NULL DROP VIEW ADSI_USERS ; GO CREATE VIEW ADSI_USERS AS SELECT * FROM OpenQuery (ADSI, 'select title, displayName, sAMAccountName, givenName, department, mail, telephoneNumber, sn, physicalDeliveryOfficeName from ''LDAP://dc=domain,dc=local'' where objectCategory=''user'' AND objectCategory = ''Person'' AND displayName = ''*'' ') GO -- Select the USERS to test the connection USE ICT Select * from ADSI_USERS To Drop a Linked server Sp_DropServer 'ADSI', 'droplogins'
Set the correct security
Set security to BE MADE USING THE CURRENT SECURITY CONTENT !
Giving the correct user and password to connect.
Create an External content type in SharePoint Designer using the ADSI data source.
And make sure that you change the Authentication Mode security of the Default and Client to BCD Identity
See here is the output.
And the beauty is that you can reuse these accounts as lookup Fields in other lists !
This makes sure you are always in sync with your AD.