nltest /dclist:sub.dom.com
results amongst others in:
\\dcserver1.sub.dom.com -> dcserver1 is the AD server
Analyse active directory structure
Use Sysinternals ADExplorer to analyse the structure of active directory. Connect to server: dcserver1
Users in a group from command prompt
net group adtestgroup /domain
Active Directory structure in FROM clause
E.g. FROM "LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com"
LDAP = case-sensitive protocol name, always this value
Before the slash: sub.dom.com, is the domain for which to find the AD structure
After the slash: the part of the tree to search. Specified from right to left. So in the example, from parent to child:
com -> dom -> sub -> Global -> Groups -> ADTestGroup, searches the nodes beneath the lowest level, which is ADTestGroup
The names ("dom", "Groups", etc), type ("OU", "CN", "DC") and depth of the levels are dynamic. So check with ADExplorer how AD is configured in the specific instance.
Users in a group from SQL Server
First, add a linked server to ADSI (fill with correct password for DOMAIN\ACCOUNT):
exec sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
exec sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = null, @rmtuser = 'DOMAIN\ACCOUNT', @rmtpassword = '********'
Select users in group "adtestgroup":
select * from openquery(ADSI,'
SELECT objectCategory, cn, sn, mail, name, department, company
FROM ''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''
WHERE MemberOf=''CN=adtestgroup,OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
ORDER BY cn
')
Unfortunately, dynamic sql is not possible inside a udf, so a procedure is needed:
alter procedure cst_usersingroup(@grp nvarchar(100))
as
begin
declare @sql as nvarchar(max) = '
select samaccountname as accountname, name, mail, department, title, company
from openquery(ADSI,''
SELECT objectCategory, cn, sn, mail, name, department, company, title, samaccountname
FROM ''''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''''
WHERE MemberOf=''''CN=' + @grp + ',OU=Groups,OU=Global,DC=sub,DC=dom,DC=com'''''')
x
order by samaccountname'
exec(@sql)
end
Select all BI_* and EDW_* groups from active directory:
select lower(cn) as grp, whencreated, whenchanged, distinguishedname
from openquery(ADSI,'
SELECT cn, distinguishedname, whencreated, whenchanged
FROM ''LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
where CN=''bi_*'' or CN=''edw_*''
') x
order by cn
Notes:
- In most cases, all domain users can query AD using the basic search method specified before. More advanced AD search methods might be disabled and require special rights