By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T(
ID INT,
UserID int,
Roles nvarchar(50)
);
INSERT INTO T VALUES (1,1,'["Admin","A1","A2"]');
INSERT INTO T VALUES (2,2,'["B1","A2"]');
SELECT
col1,
UserID
FROM T t1
CROSS APPLY
OPENJSON(t1.Roles)
WITH
(
col1 varchar(50) N'$'
) AS a
| col1 | UserID |
|---|---|
| Admin | 1 |
| A1 | 1 |
| A2 | 1 |
| B1 | 2 |
| A2 | 2 |