title: Query AD Group Membership Recursively Through SQL
body: <p><strong>Background</strong></p>
  
  <p>I'm creating some SQL to assist with security auditing; this will take security info from various systems databases and from Active Directory and will produce a list of all anomalies (i.e. cases where accounts are closed in one system but not others.</p>
  
  <p><strong>Current Code</strong></p>
  
  <p>To get a list of users who are a member of a security group I run the below SQL:</p>
  
  <pre><code>if not exists(select 1 from sys.servers where name = 'ADSI') 
      EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
  
  SELECT sAMAccountName, displayName, givenName, sn, isDeleted --, lastLogonTimestamp --, lastLogon (Could not convert the data value due to reasons other than sign mismatch or overflow.)
  FROM OPENQUERY(ADSI
  , 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted
  FROM ''LDAP://DC=myDomain,DC=myCompany,DC=com''
  WHERE objectCategory = ''Person''
  AND objectClass = ''user'' 
  AND memberOf = ''CN=mySecurityGroup,OU=Security Groups,OU=UK,DC=myDomain,DC=myCompany,DC=com''
  ')
  order by sAMAccountName
  </code></pre>
  
  <p><strong>Problem / Question</strong></p>
  
  <p>I'd like this code to be able to work recursively; i.e. if a user is a member of a group which is a member of the specified group, they should be included too (for the full hierarchy).  Does anyone know how to do this through SQL?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I've now resolved a few issues (not related to the quoted problem, but some other issues I'd had).</p>
  
  <ul>
  <li>lastLogon was throwning an error.  This was because the server version was x86.  Using an x64 database resolved the problem.</li>
  <li>lastLogon was returned as a number.  Added some code to convert this to DateTime2.</li>
  <li>I was able to move the group name out of a hard coded string by making OpenQuery itself dynamic, so within the context of OpenQuery the generated string looks static.</li>
  </ul>
  
  <p>..</p>
  
  <pre><code>--create linked server
  if not exists(select 1 from sys.servers where name = 'ADSI')
  begin
      --EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
      EXEC master.dbo.sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
      EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
  end
  
  
  declare @path nvarchar(1024) = 'DC=myDomain,DC=myCompany,DC=com'
  declare @groupCN nvarchar(1024) = 'CN=My Security Group,OU=Security Groups,OU=UK,' + @path
  , @sql nvarchar(max)
  
  --construct the query we send to AD
  set @sql = '
  SELECT sAMAccountName, displayName, givenName, sn, isDeleted, lastLogon
  FROM ''LDAP://' + replace(@path,'''','''''') + '''
  WHERE objectCategory = ''Person''
  AND objectClass = ''user'' 
  AND memberOf = ''' + replace(@groupCN,'''','''''') + '''
  '
  
  --now wrap that query in the outer query
  set @sql = 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted
  , case     
      when cast([lastLogon] as bigint) = 0 then null
      else dateadd(mi,(cast([lastlogon] as bigint) / 600000000), cast(''1601-01-01'' as datetime2)) 
    end LastLogon
  FROM OPENQUERY(ADSI, ''' + replace(@sql,'''','''''') + ''')
  order by sAMAccountName'
  
  --now run it
  exec(@sql)
  </code></pre>