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 


Get all users from an AD Group

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[GetLdapUserGroups]    Script Date: 5/20/2022 2:53:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUsersFromGroup]
    (
    --@LdapUsername NVARCHAR(max),
    @groupname NVARCHAR(max)
    )
AS
BEGIN
DECLARE @Query NVARCHAR(max)

SET @Query = '
    SELECT sAMAccountName AS [LdapUsersFromGroup]
    FROM OPENQUERY (ADSI, ''<LDAP://snet.crouse.org>;
    (&(objectClass=person)(memberOf:1.2.840.113556.1.4.1941:= ' + @groupname + '));
    cn,sAMAccountName, adspath;subtree'')
   
    ORDER BY cn;
'
EXEC SP_EXECUTESQL @Query

--exec [dbo].[GetUsersFromGroup] @groupname='CN=VPN-AlwaysOn,OU=DomainGroups,DC=snet,DC=crouse,DC=org'
--exec [dbo].[GetUsersFromGroup] @groupname='CN=GRP-IT,OU=Department Groups,DC=snet,DC=crouse,DC=org'
END
GO

sGrabBetween

/****** Object:  UserDefinedFunction [dbo].[sGrabBetween]    Script Date: 1/15/2021 11:53:52 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

  ALTER function [dbo].[sGrabBetween](

  @Text as nvarchar(MAX),

  @First as nvarchar,

  @Second as nvarchar

)

  RETURNS nvarchar(MAX)

AS

BEGIN


DECLARE @nIndexStart INT

DECLARE @nIndexEnd INT

DECLARE @return nvarchar(MAX)

              SET @nIndexStart  = CHARINDEX(@First,@Text,0)

        SET @nIndexEnd   =  CHARINDEX(@Second,@Text,@nIndexStart+1)

/*            PRINT @nIndexStart

              PRINT @nIndexEnd

              PRINT @Text

              */

              IF @nIndexStart > 0 AND @nIndexEnd > 0

               SET @return =  Substring(@Text,@nIndexStart + len(@First) , @nIndexEnd - @nIndexStart-1)

        ELSE

                SET @return=''

RETURN @return


END

--select dbo.sgrabbetween('15.0.4083.2','.','.')


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

Disable Change Tracking

DECLARE @dbname VARCHAR(128)
DECLARE @AutoClnup TINYINT
DECLARE @AutoClnupStr VARCHAR(3)
DECLARE @RetPer INT
DECLARE @RetPerUnitsDesc VARCHAR(60)
DECLARE @schema VARCHAR(128)
DECLARE @tblname VARCHAR(128)
DECLARE @ColUpdtFlg TINYINT
DECLARE @ColUpdt VARCHAR(5)
DECLARE @sqlstr1 NVARCHAR(2000)
DECLARE @sqlstr2 NVARCHAR(2000)
DECLARE @sqlstr3 NVARCHAR(2000)
DECLARE @sqlstr4 NVARCHAR(2000)
DECLARE tblcur CURSOR FOR
SELECT S.name AS SchemaName,
OBJECT_NAME(T.object_id) AS TableName,
T.is_track_columns_updated_on
FROM sys.change_tracking_tables T
INNER JOIN sys.tables TT ON TT.object_id = T.object_id
INNER JOIN sys.schemas S ON S.schema_id = TT.schema_id
ORDER BY SchemaName, TableName
SET @dbname = DB_NAME()
SELECT @AutoClnup = is_auto_cleanup_on,
@RetPer = retention_period ,
@RetPerUnitsDesc = retention_period_units_desc
FROM sys.change_tracking_databases WHERE database_id = DB_ID()
IF @AutoClnup = 1
SET @AutoClnupStr = 'ON'
ELSE
SET @AutoClnupStr = 'OFF'
OPEN tblcur
FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg
— disable statements
PRINT '–BEGIN DISABLE CHANGE TRACKING STATEMENTS'
WHILE @@FETCH_STATUS=0
BEGIN
SET @sqlstr1 = 'ALTER TABLE ['+@schema+'].['+@tblname+'] DISABLE CHANGE_TRACKING'
PRINT @sqlstr1
FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg
END
CLOSE tblcur
SET @sqlstr2 = '
ALTER DATABASE ['+@dbname+'] SET CHANGE_TRACKING = OFF'
PRINT @sqlstr2
PRINT '–END DISABLE CHANGE TRACKING STATEMENTS
'
— open it again to create enable statements
OPEN tblcur
FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg
— enable statements
PRINT '–BEGIN ENABLE CHANGE TRACKING STATEMENTS'
SET @sqlstr3 = 'ALTER DATABASE ['+@dbname+'] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = '+CAST(@RetPer AS VARCHAR(4))+' '+@RetPerUnitsDesc+', AUTO_CLEANUP = '+@AutoClnupStr+')
'
PRINT @sqlstr3
WHILE @@FETCH_STATUS=0
BEGIN
IF @ColUpdtFlg = 1
SET @ColUpdt = 'ON'
ELSE
SET @ColUpdt = 'OFF'
SET @sqlstr4 = 'ALTER TABLE ['+@schema+'].['+@tblname+'] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = '+@ColUpdt+')'
PRINT @sqlstr4
FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg
END
PRINT '–END DISABLE CHANGE TRACKING STATEMENTS'
CLOSE tblcur
DEALLOCATE tblcur
GO
https://www.sqlrx.com/how-enabling-change-tracking-can-cause-backup-failure/
x