Loop Through bitarray to retrieve subsets of that bitarray
You're definitely on the right track by using a "Numbers" table, rather than a loop or cursor. Given the small size and the fact that you know up front that you only need 24 rows, there's no need to calculate it. You can also save a bit my creating the numbers table on the fly.

What you have now should already be reasonably fast but the in-line tally table and hard coded number of rows should beat it... If only by a small margin.

DECLARE @Ba NVARCHAR(48) = '000011110000000001110000000011111111000011110000'
DECLARE @numberOfIntervals INT = 24;
DECLARE @intervals TABLE( 
    SequenceId INT,
    [Periods] NVARCHAR(24)
);

WITH 
    cte_n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1) ) n (n)), 
    cte_Tally (n) AS (
        SELECT TOP (24)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n a CROSS JOIN cte_n b
        )
INSERT INTO @intervals
SELECT
    t.n - 1 AS [SequenceId],
    SUBSTRING(@Ba, t.n, @numberOfIntervals) AS [Values]
FROM
    cte_Tally t
WHERE
    SUBSTRING(@Ba, (t.n - 1)  + @numberOfIntervals, 1) = '1';

SELECT
    *
FROM
    @intervals;