locked
ADSI queries or EXEC XP_LOGININFO - getting list of members in an Active Directory Group RRS feed

  • Question

  • I am trying to get a list of "members" in Active Directory Groups at the Domain Server

    This used to work in my previous companies.  Don't know why but only "SQL Server Administrators" works..... as a member of the DBA group, I can get the list of DBA accounts, but ends up with empty results for other groups.

    Obviously, I am not a member of the domain admins.  There has to be Domain Server Level security on who can see what kind of AD data, right?

    Strangely enough, command line queries behave the same way.

    Friday, June 16, 2017 3:20 PM

All replies

  • As command line queries work the same as ADSI queries, this sounds like a permissions issue.

    What is returned when you do this?

    	DECLARE @OPENQUERY nvarchar(4000), @LinkedServer nvarchar(4000),
    			@LDAPServer nvarchar(4000), @ADUser nvarchar(4000),
    			@SecurityGroup nvarchar(4000), @IsMemberOfGroup bit
                  
    SET @LinkedServer = 'ADSI'
    SET @LDAPServer = 'LDAP://PutYourDomainHere.local'
    
    --SET @SecurityGroup = 'CMSAdmin DB Security Group'
    
    SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
    SET @OPENQUERY = @OPENQUERY +
                                             'SELECT * FROM ''''' + @LDAPServer + '''''
                                             WHERE objectCategory=''''person'''' 
                                             AND objectClass=''''*'''' 
                               '')'
    
    --SELECT @OPENQUERY
    EXECUTE sp_executesql @OPENQUERY, 
                                        N'@IsMemberOfGroup bit OUTPUT', 
                                        @IsMemberOfGroup OUTPUT
     

    You might get an error at some point, but you should get the bulk of your results returned.

    Friday, June 16, 2017 3:34 PM
  • It gives me this.... but I sort of deleted the ADSI Linked Server.

    And then not sure the code that I have kept is the latest and the greatest.

    Without being sure if I am doing the right thing.....I get this..... but it has worked before.... afraid the syntax and DC name is wrong, etc.

    Msg 7399, Level 16, State 1, Line 29

    The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

    Obviously the code that you have posted is not working either, regardless of the error message.  Because the error message will not mean much if the ADSI Linked server is not right.... will try the command line, based on this info.... and will watch one of the domain admins do the command line..... paaaaaaaaaaaaaaaaain!!!


    Travis McGee

    Friday, June 16, 2017 8:56 PM