Test if a user is in a group by using a SQL Stored Procedure

        const adinteger = 3

        const adparaminput = 1

        const adParamOutput =2

        const advarchar = 200

        const addbtimestamp=135

        conn_string2 = "provider=sqloledb;data source=" & "database\instance" & ";trusted_connection=no;initial catalog=" & "crousescripts" & ";uid=username;password=strongpassword;"

        set conn = server.createobject("adodb.connection")

        conn.open conn_string2

        Set cmd2 = Server.CreateObject("ADODB.Command")

        Set RS2 = CreateObject("ADODB.Recordset")

        With cmd2

            .ActiveConnection = conn

            .CommandType = 4 'SP

            .CommandText = "[master].[dbo].[GetLdapUserGroups]"

            .Parameters.Append cmd2.CreateParameter("@LdapUsername", adVarChar, adParamInput, 20, vc_employeeID)

            .Parameters.Append cmd2.CreateParameter("@groupname", adVarChar, adParamInput, 20, "GRP-DUOPreGame")

        End with

        Set RS2 =  cmd2.Execute

                                'response.write RS2.eof' & "" & " " & RS2("LdapGroup")

            if RS2.eof then

            response.end

            else

                ' Response.Write "<p>Result (in Group) = " & RS2("LdapGroup") & "</p>"

            end if

[master].[dbo].[GetLdapUserGroups]

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[GetLdapUserGroups]    Script Date: 11/15/2020 1:19:11 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GetLdapUserGroups]

    (

    @LdapUsername NVARCHAR(max),

       @groupname NVARCHAR(max)

    )

AS

BEGIN

DECLARE @Query NVARCHAR(max), @Path NVARCHAR(max)

SET @Query = '

    SELECT @Path = distinguishedName

    FROM OPENQUERY(ADSI, ''

        SELECT distinguishedName

        FROM ''''LDAP://DC=example,DC=com''''

        WHERE

            objectClass = ''''user'''' AND

            sAMAccountName = ''''' + @LdapUsername + '''''

    '')

'

EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(max) OUTPUT', @Path = @Path OUTPUT

  SET @Query = '

    SELECT cn AS [LdapGroup]

    FROM OPENQUERY (ADSI, ''<LDAP://example.com >;

    (&(objectClass=group)(member:1.2.840.113556.1.4.1941:= ' + @Path + '));

    cn, adspath;subtree'')

       WHERE cn like '''+@groupname+'''

    ORDER BY cn;

'

EXEC SP_EXECUTESQL @Query

END

GO