Cacher is the code snippet organizer for pro developers

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\login',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

-- for more details: http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-t-sql/

-- may also need to increase MaxpageSize in AD from 1000 to... 2500?
-- otherwise your queries can only return 1000 records at a time.
-- http://support.microsoft.com/kb/315071

-- query all groups
SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''Group'' ')
	
-- query all users
SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, samAccountName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''User''  ') where samAccountName not like '%$'

-- to query ALL group memberships, you'll have to cursor through
-- all groups and get their members with a statement like this:
select * from OPENQUERY( ADSI, 'SELECT objectGuid, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''user'' and memberof=''CN=[group name],OU=[org unit],DC=mydomain,DC=local''  ') 
  
-- replace "mydomain" and possibly "local" above with your domain (e.g. myco.local, yourco.com, etc)