LDAP - Check if a user is in a group

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 


sGrabBetween

create procedure sGrabBetween

@Text as nvarchar,

@First as nvarchar,

@Second as nvarchar

AS

SELECT SUBSTRING(@Text, CHARINDEX(@First, @Text) + LEN(@First),

                 CHARINDEX(@Second, @Text) - CHARINDEX(@First, @Text) - LEN(@First))


Another one


SELECT SUBSTRING(

    @Text,

    CHARINDEX(@charBegin, @Text) + 1,

    --LEN(@Text) - CHARINDEX(@charBegin, @Text) - CHARINDEX(@charEnd, REVERSE(@Text)))

       LEN(@Text) - CHARINDEX(@charBegin, @Text) - CHARINDEX(@charEnd, REVERSE(@Text)))

EXEC results to a variable

alter proc testReturn

AS

BEGIN

SELECT 'hi'

end

DECLARE @out NVARCHAR(MAX)

EXEC @out = testReturn

PRINT @out

Enable Server Wide Backup Compression

EXEC sys.sp_configure N'backup compression default', N'1'

GO

RECONFIGURE WITH OVERRIDE

GO