tło

Tworzę SQL, aby pomóc w kontroli bezpieczeństwa; Spowoduje to pobranie informacji o bezpieczeństwie z różnych baz danych systemów i z Active Directory oraz wygeneruje listę wszystkich anomalii (tj. przypadków, w których konta są zamknięte w jednym systemie, ale nie w innych).

Aktualny kod

Aby uzyskać listę użytkowników, którzy są członkami grupy bezpieczeństwa, uruchamiam poniższy kod SQL:

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

Problem / pytanie

Chciałbym, aby ten kod działał rekurencyjnie; tj. jeśli użytkownik jest członkiem grupy, która jest członkiem określonej grupy, należy go również uwzględnić (dla pełnej hierarchii). Czy ktoś wie, jak to zrobić za pomocą SQL?

AKTUALIZACJA

Rozwiązałem teraz kilka problemów (nie związanych z cytowanym problemem, ale kilka innych problemów, które miałem).

lastLogon zgłosił błąd. To dlatego, że wersja serwera była x86. Korzystanie z bazy danych x64 rozwiązało problem.lastLogon został zwrócony jako liczba. Dodano kod do konwersji na DateTime2.Udało mi się przenieść nazwę grupy z zakodowanego łańcucha, czyniąc OpenQuery samą dynamiczną, więc w kontekście OpenQuery wygenerowany ciąg wygląda statycznie.

..

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