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