SQL query to run LDAP query to return AD listing of active users and then groups they are assigned to that start with GRP-XP%

I am trying to write a sql statement to control a report header selection of multiple database by user groups a user is assigned to. This is to limit the database selection security when running an SSRS report so that they can’ only select their branch or group of branches they have access to. So far I can return the group results for a single user. I am trying to get a list of all active AD users and groups that are like GRP-XP%. Here is my script so far, that only works for a single username. Eventually this table will be passed to PowerBI, so I’d need username, usergroup table of listing complete.

Declare @username varchar(max) = 'ssmith'
DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)

SET @Query = '
    SELECT @Path = distinguishedName
    FROM OPENQUERY(CSAD, ''
        SELECT distinguishedName, SAMAccountName 
        FROM ''''LDAP://DC=Domain,DC=co, dc=uk''''
        WHERE 
            objectClass = ''''user'''' 
            AND sAMAccountName = ''''' + Replace(@Username, 'domain', '') + '''''
    '')
'
EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT 

SET @Query = '
    SELECT
    Replace(Right(cn, Len(cn)-7), '' '', '' '')
    FROM OPENQUERY (CSAD, ''<LDAP://DC=Domain,DC=co,DC=uk>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));cn, adspath;subtree'')
    where CN like ''GRP-XP%'' 
    Order By cn'
Declare @table Table (Name varchar(100))
Insert into @table
EXEC SP_EXECUTESQL @Query  
select * from @table

So the results would look like;

Thank you

Answer

I found some code I wrote many years ago. I can’t test it as we no longer use a local AD domain controller but it definitely worked at some point.

It basically returns a list of all users and associated groups so it should be easy enough to modify for your needs.

ALTER PROC [AD].[Get_AD_AllUsersWithGroups]
AS
DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)
DECLARE @distinguishedName nvarchar(256)
DECLARE @SAMAccountName nvarchar(256)

CREATE TABLE #users (distinguishedName nvarchar(1000), SAMAccountName nvarchar(100))
CREATE TABLE #results(SAMAccountName nvarchar(100), DistinguishedName nvarchar(1000), GroupName nvarchar(1000), ActiveDirectoryPath nvarchar(1000))

-- Get all the users from AD
SET @Query = '
   SELECT distinguishedName, SAMAccountName
   FROM OPENQUERY(ADSI, ''
       SELECT distinguishedName , SAMAccountName
       FROM ''''LDAP://DC=MyDomain,DC=local''''
       WHERE 
           objectClass = ''''user'''' 
   '')
'



INSERT INTO #users
EXEC SP_EXECUTESQL @Query

-- For each user in #users, get a list of groups they belong to
DECLARE cUsers CURSOR FOR
    SELECT distinguishedName, SAMAccountName from dbo.#users u 
        order by u.distinguishedName

OPEN cUsers

FETCH NEXT FROM cUsers
INTO @distinguishedName, @SAMAccountName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @distinguishedName = REPLACE(@distinguishedName, '''', '''''')
    SET @SAMAccountName = REPLACE(@SAMAccountName, '''', '''''')
    
    SET @Query = '
        INSERT INTO #results
        SELECT ''' + @SAMAccountName + ''', ''' + @distinguishedName + ''', cn as GroupName, AdsPath AS ActiveDirectoryPath
        FROM OPENQUERY (ADSI, ''<LDAP://DC=MyDomain,DC=local>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' 
       + @distinguishedName +'));cn, adspath;subtree'')'

    EXEC SP_EXECUTESQL @Query  


    FETCH NEXT FROM cUsers
    INTO @distinguishedName, @SAMAccountName
END

CLOSE cUsers
DEALLOCATE cUsers

SELECT * FROM dbo.#results r

GO