Post reply

ADSI users and groups query

  • February 23, 2018 at 1:19 pm

    #336335

    Wasn't quite sure where to put this, but am hoping someone can help...  

    I have the following query which I mostly snagged from a blog somewhere.  It works just fine...  it finds all of the AD groups on our domain, and all of the users who are in each group. 

    My challenge is, we have another trusted domain and we have users from that domain that are in some of our groups.  The query doesn't find them because they technically aren't "users" as far as LDAP is concerned; they're FSPs (foreign security principles) and therefore don't get picked up. 

    Was wondering if anyone know how, via SQL query, to include the FSPs along with the "real" users.

    if object_id('tempdb.dbo.#MemberOfDomains') is not null drop table #MemberOfDomains

    create table #MemberOfDomains(
    ADDomainName varchar(400),
    cn varchar(400),
    displayName varchar(400),
    sAMAccountName varchar(200),
    physicalDeliveryOfficeName varchar(200),
    userAccountControl int
    )
    SET NOCOUNT ON
    declare @t varchar(100),@t2 varchar(1000), @ot varchar (4000), @tt varchar (4000);

    declare gC cursor
    for
    select cn, distinguishedName
    from openquery
    (ADSI,'SELECT cn, distinguishedName
    FROM ''LDAP://OU=myDomain,DC=myDom,DC=myDomain,DC=com''
    WHERE objectCategory = ''Domain''')
    open gC

    FETCH NEXT FROM gC INTO @t, @t2
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @ot = '''SELECT cn, displayName, sAMAccountName, physicalDeliveryOfficeName, userAccountControl
    FROM ''''LDAP://OU=myDomain,DC=myDom,DC=myDomain,DC=com''''
    WHERE memberOf=''''' + @t2 + '''''';
    set @tt = 'select '+ ''''+@t+'''' +' As DomainName, cn, displayName, sAMAccountName, physicalDeliveryOfficeName, userAccountControl from openquery(ADSI,'+ @ot +''') order by cn'

    insert into #MemberOfDomains(ADDomainName, cn, displayName, sAMAccountName, physicalDeliveryOfficeName, userAccountControl)
    EXEC (@tt)
    --print @tt
    FETCH NEXT FROM gC INTO @t, @t2
    END
    CLOSE gC
    DEALLOCATE gC

    select
        *
    from #MemberOfDomains

  • February 23, 2018 at 1:59 pm

    #1981005

    Does sys.xp_logininfo return the FSPs?

  • February 23, 2018 at 3:34 pm

    #1981016

    It does...  but unfortunately it only seems to work if the AD group has been created as a login on the SQL server...   I need to do all of the groups on the domain.

  • February 26, 2018 at 10:01 am

    #1981136

    So create logins for al the groups in the domain

  • February 26, 2018 at 12:45 pm

    #1981175

    Joe Torre - Monday, February 26, 2018 10:01 AM

    So create logins for al the groups in the domain

    That's actually one of the things we're considering...  but we'd rather not do that unless it's absolutely necessary.  There are hundreds of these groups and no good reason for most of them to have logins on our SQL instances.  

    One of our programmers claims he may have a way of doing what we need via C# code so I'm going to see how that works out.

    thanks 😀

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply