alter 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=YOUR,DC=DOMAIN''''
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://YOUR.DOMAIN>;
(&(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
exec [master].[dbo].[GetLdapUserGroups] @LdapUsername='richlemmermann',@groupname ='grp-it'
GRANT EXECUTE ON [master].[dbo].[GetLdapUserGroups] TO [User]
https://stackoverflow.com/questions/13914698/query-ad-group-membership-recursively-through-sql
/****** Object: UserDefinedFunction [dbo].[sGrabBetween] Script Date: 1/15/2021 11:53:52 AM ******/
--select dbo.sgrabbetween('15.0.4083.2','.','.')
alter proc testReturn
AS
BEGIN
SELECT 'hi'
end
DECLARE @out NVARCHAR(MAX)
EXEC @out = testReturn
PRINT @out
EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO