fundo

Estou criando algum SQL para ajudar na auditoria de segurança; Isso levará informações de segurança de vários bancos de dados de sistemas e do Active Directory e produzirá uma lista de todas as anomalias (ou seja, casos em que as contas são fechadas em um sistema, mas não em outras).

Código atual

Para obter uma lista de usuários que são membros de um grupo de segurança, executo o SQL abaixo:

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

Problema / Questão

Eu gostaria que este código pudesse funcionar recursivamente; ou seja, se um usuário for membro de um grupo que seja membro do grupo especificado, ele também deverá ser incluído (para a hierarquia completa). Alguém sabe como fazer isso através do SQL?

ATUALIZAR

Agora resolvi alguns problemas (não relacionados ao problema citado, mas a alguns outros problemas que tive).

lastLogon estava lançando um erro. Isso ocorreu porque a versão do servidor era x86. Usando um banco de dados x64 resolveu o problema.lastLogon foi retornado como um número. Adicionado algum código para converter isso em DateTime2.Consegui mover o nome do grupo para fora de uma string codificada, tornando o próprio OpenQuery dinâmico, portanto, dentro do contexto do OpenQuery, a string gerada parece estática.

..

--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)