SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');
insert into tbltest values('Nasir Ahmed Amir Ahmed Mehendy','UAE High Street');

CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

--Table variable to identity column for each words
DECLARE @Inter TABLE (id int identity(1,1), names varchar(max));

--Insert into table variable
INSERT INTO @Inter(names) SELECT name from TblTest;

--Query to insert into NameParts table
;WITH splitdata 
AS 
(
    SELECT id,splitname 
    FROM   
    (
        SELECT *,Cast('<X>' + Replace(F.Names, ' ', '</X><X>') + '</X>' AS XML) AS  xmlfilter 
        FROM   @Inter F
    )F1 
    CROSS apply 
    (
        SELECT fdata.d.value('.', 'varchar(50)') AS splitName 
        FROM   f1.xmlfilter.nodes('X') AS fdata(d)
    ) O
) 
INSERT INTO dbo.NameParts
SELECT id,STUFF([splitdata].[splitName] ,ROW_NUMBER() OVER (PARTITION BY [splitdata].[id] ORDER BY [splitdata].[splitName] ASC),0,'~')
FROM splitdata;

;WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  REPLACE(FullNameVariation,'~','')
FROM
  permutations
WHERE 1=1 AND
  Level = 1
ORDER BY
  ID,
  FullNameVariation 
OPTION (MAXRECURSION 5100);

DROP TABLE [dbo].[tbltest]
DROP TABLE [dbo].[NameParts]

ID	(No column name)
1	John Mak Sam
1	John Sam Mak
1	Mak John Sam
1	Mak Sam John
1	Sam John Mak
1	Sam Mak John
2	Donatella Nobatti
2	Nobatti Donatella
3	Buca Sam
3	Sam Buca
4	Juan Soponatime
4	Soponatime Juan
5	Aaron Spacemuseum
5	Spacemuseum Aaron
6	Ahmed Ahmed Amir Mehendy Nasir
6	Ahmed Ahmed Amir Nasir Mehendy
6	Ahmed Ahmed Mehendy Amir Nasir
6	Ahmed Ahmed Mehendy Nasir Amir
6	Ahmed Ahmed Nasir Amir Mehendy
6	Ahmed Ahmed Nasir Mehendy Amir
6	Ahmed Amir Ahmed Mehendy Nasir
6	Ahmed Amir Ahmed Nasir Mehendy
6	Ahmed Amir Mehendy Ahmed Nasir
6	Ahmed Amir Mehendy Nasir Ahmed
6	Ahmed Amir Nasir Ahmed Mehendy
6	Ahmed Amir Nasir Mehendy Ahmed
6	Ahmed Mehendy Ahmed Amir Nasir
6	Ahmed Mehendy Ahmed Nasir Amir
6	Ahmed Mehendy Amir Ahmed Nasir
6	Ahmed Mehendy Amir Nasir Ahmed
6	Ahmed Mehendy Nasir Ahmed Amir
6	Ahmed Mehendy Nasir Amir Ahmed
6	Ahmed Nasir Ahmed Amir Mehendy
6	Ahmed Nasir Ahmed Mehendy Amir
6	Ahmed Nasir Amir Ahmed Mehendy
6	Ahmed Nasir Amir Mehendy Ahmed
6	Ahmed Nasir Mehendy Ahmed Amir
6	Ahmed Nasir Mehendy Amir Ahmed
6	Ahmed Ahmed Amir Mehendy Nasir
6	Ahmed Ahmed Amir Nasir Mehendy
6	Ahmed Ahmed Mehendy Amir Nasir
6	Ahmed Ahmed Mehendy Nasir Amir
6	Ahmed Ahmed Nasir Amir Mehendy
6	Ahmed Ahmed Nasir Mehendy Amir
6	Ahmed Amir Ahmed Mehendy Nasir
6	Ahmed Amir Ahmed Nasir Mehendy
6	Ahmed Amir Mehendy Ahmed Nasir
6	Ahmed Amir Mehendy Nasir Ahmed
6	Ahmed Amir Nasir Ahmed Mehendy
6	Ahmed Amir Nasir Mehendy Ahmed
6	Ahmed Mehendy Ahmed Amir Nasir
6	Ahmed Mehendy Ahmed Nasir Amir
6	Ahmed Mehendy Amir Ahmed Nasir
6	Ahmed Mehendy Amir Nasir Ahmed
6	Ahmed Mehendy Nasir Ahmed Amir
6	Ahmed Mehendy Nasir Amir Ahmed
6	Ahmed Nasir Ahmed Amir Mehendy
6	Ahmed Nasir Ahmed Mehendy Amir
6	Ahmed Nasir Amir Ahmed Mehendy
6	Ahmed Nasir Amir Mehendy Ahmed
6	Ahmed Nasir Mehendy Ahmed Amir
6	Ahmed Nasir Mehendy Amir Ahmed
6	Amir Ahmed Ahmed Mehendy Nasir
6	Amir Ahmed Ahmed Nasir Mehendy
6	Amir Ahmed Mehendy Ahmed Nasir
6	Amir Ahmed Mehendy Nasir Ahmed
6	Amir Ahmed Nasir Ahmed Mehendy
6	Amir Ahmed Nasir Mehendy Ahmed
6	Amir Ahmed Ahmed Mehendy Nasir
6	Amir Ahmed Ahmed Nasir Mehendy
6	Amir Ahmed Mehendy Ahmed Nasir
6	Amir Ahmed Mehendy Nasir Ahmed
6	Amir Ahmed Nasir Ahmed Mehendy
6	Amir Ahmed Nasir Mehendy Ahmed
6	Amir Mehendy Ahmed Ahmed Nasir
6	Amir Mehendy Ahmed Nasir Ahmed
6	Amir Mehendy Ahmed Ahmed Nasir
6	Amir Mehendy Ahmed Nasir Ahmed
6	Amir Mehendy Nasir Ahmed Ahmed
6	Amir Mehendy Nasir Ahmed Ahmed
6	Amir Nasir Ahmed Ahmed Mehendy
6	Amir Nasir Ahmed Mehendy Ahmed
6	Amir Nasir Ahmed Ahmed Mehendy
6	Amir Nasir Ahmed Mehendy Ahmed
6	Amir Nasir Mehendy Ahmed Ahmed
6	Amir Nasir Mehendy Ahmed Ahmed
6	Mehendy Ahmed Ahmed Amir Nasir
6	Mehendy Ahmed Ahmed Nasir Amir
6	Mehendy Ahmed Amir Ahmed Nasir
6	Mehendy Ahmed Amir Nasir Ahmed
6	Mehendy Ahmed Nasir Ahmed Amir
6	Mehendy Ahmed Nasir Amir Ahmed
6	Mehendy Ahmed Ahmed Amir Nasir
6	Mehendy Ahmed Ahmed Nasir Amir
6	Mehendy Ahmed Amir Ahmed Nasir
6	Mehendy Ahmed Amir Nasir Ahmed
6	Mehendy Ahmed Nasir Ahmed Amir
6	Mehendy Ahmed Nasir Amir Ahmed
6	Mehendy Amir Ahmed Ahmed Nasir
6	Mehendy Amir Ahmed Nasir Ahmed
6	Mehendy Amir Ahmed Ahmed Nasir
6	Mehendy Amir Ahmed Nasir Ahmed
6	Mehendy Amir Nasir Ahmed Ahmed
6	Mehendy Amir Nasir Ahmed Ahmed
6	Mehendy Nasir Ahmed Ahmed Amir
6	Mehendy Nasir Ahmed Amir Ahmed
6	Mehendy Nasir Ahmed Ahmed Amir
6	Mehendy Nasir Ahmed Amir Ahmed
6	Mehendy Nasir Amir Ahmed Ahmed
6	Mehendy Nasir Amir Ahmed Ahmed
6	Nasir Ahmed Ahmed Amir Mehendy
6	Nasir Ahmed Ahmed Mehendy Amir
6	Nasir Ahmed Amir Ahmed Mehendy
6	Nasir Ahmed Amir Mehendy Ahmed
6	Nasir Ahmed Mehendy Ahmed Amir
6	Nasir Ahmed Mehendy Amir Ahmed
6	Nasir Ahmed Ahmed Amir Mehendy
6	Nasir Ahmed Ahmed Mehendy Amir
6	Nasir Ahmed Amir Ahmed Mehendy
6	Nasir Ahmed Amir Mehendy Ahmed
6	Nasir Ahmed Mehendy Ahmed Amir
6	Nasir Ahmed Mehendy Amir Ahmed
6	Nasir Amir Ahmed Ahmed Mehendy
6	Nasir Amir Ahmed Mehendy Ahmed
6	Nasir Amir Ahmed Ahmed Mehendy
6	Nasir Amir Ahmed Mehendy Ahmed
6	Nasir Amir Mehendy Ahmed Ahmed
6	Nasir Amir Mehendy Ahmed Ahmed
6	Nasir Mehendy Ahmed Ahmed Amir
6	Nasir Mehendy Ahmed Amir Ahmed
6	Nasir Mehendy Ahmed Ahmed Amir
6	Nasir Mehendy Ahmed Amir Ahmed
6	Nasir Mehendy Amir Ahmed Ahmed
6	Nasir Mehendy Amir Ahmed Ahmed