Active Directory to MSSQL

1. Create a database (here it is OPE), with required columns.
2.  Open a ‘New Query’  window in  Microsoft SQL Server Management Studio Express and put the below SQL Code there.
3. Find and Replace(Ctrl+H) all occurance of DC=epillars and DC=local with the appropriate values from the Active Directory.
4. Execute(F5) the Query.
5. The Query will create a Linked Server ADSI(Server Objects->Linked Servers->ADSI) and a view  ADView(Databases->OPE->Views->dbo.ADView) which contains users on active directory. Then the Users table in OPE database will be updated with the active directory users details.

USE [master]
GO
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
GO

USE OPE
GO

CREATE  VIEW ADView
AS
SELECT     CAST(LOWER(SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 3, 8) + '-' + SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 11, 4)
                      + '-' + SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 15, 4) + '-' + SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 16, 4)
                      + + '-' + RIGHT(master.dbo.fn_varbintohexstr(objectGUID), 12)) AS uniqueidentifier) AS GUID, sAMAccountName AS Name, NULL AS Password,
                      cn AS FullName, userPrincipalName AS Email, CAST ((SELECT GUID FROM Roles WHERE (Name = 'default')) AS uniqueidentifier) AS RoleGUID, 'True' AS IsActive, 'False' AS IsAdmin, '1' AS UserType, 'False' AS ResetPwd,
                      '' AS OrderNo, GETDATE() AS PasswordCreateTime
FROM         OPENQUERY(ADSI,'SELECT  userPrincipalName ,cn, sAMAccountName , objectGUID FROM ''LDAP://DC=epillars,DC=local'' WHERE objectCategory=''user''AND NOT((MemberOf=''CN=Administrators,CN=Builtin,DC=epillars,DC=local'' OR MemberOf=''CN=Guests,CN=Builtin,DC=epillars,DC=local'' OR MemberOf=''CN=IIS_WPG,CN=Users,DC=epillars,DC=local'' ))  ')
GO

USE OPE
insert into Users (GUID,Name,FullName,Email,RoleGUID,IsActive,IsAdmin,UserType,ResetPwd,PasswordCreateTime) select a.GUID,a.Name,a.FullName,a.Email,a.RoleGUID,a.IsActive,a.IsAdmin,a.UserType,a.ResetPwd,a.PasswordCreateTime from ADView a  where not exists (select 0 from Users b where b.Name=a.Name)select * from Users