Create users dynamically from a table variable

Question

I'd like to create a T-SQL script that iterates through all the values of a table variable, creates users, and grants them access.

Think of it like this:

DECLARE @MyList TABLE (Value NVARCHAR(150))
INSERT INTO @MyList VALUES ('User0'), ('User1'), ('User2'), ('User3')

DECLARE @cnt INT = 0;
WHILE @cnt < COUNT(@MyList)
    CREATE USER [@MyList[@cnt]] FROM EXTERNAL PROVIDER;
    GRANT XXXX to [@MyList[@cnt]]
    SET @cnt = @cnt + 1
END

Is that even possible?

Answer 1

In SQL Server 2017 and higher (or Azure SQL DB/MI), you can do:

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CONVERT(nvarchar(max), 
  CONCAT(N'CREATE USER ', QUOTENAME(Value), N' FROM EXTERNAL PROVIDER;',
  char(13)+char(10),
  'GRANT XXXX TO ', QUOTENAME(Value), ';')), char(13)+char(10))
FROM @MyList;

PRINT @sql;
/* EXECUTE sys.sp_executesql @sql; */

Results:

CREATE USER [User0] FROM EXTERNAL PROVIDER;
GRANT XXXX TO [User0];
CREATE USER [User1] FROM EXTERNAL PROVIDER;
GRANT XXXX TO [User1];
CREATE USER [User2] FROM EXTERNAL PROVIDER;
GRANT XXXX TO [User2];
CREATE USER [User3] FROM EXTERNAL PROVIDER;
GRANT XXXX TO [User3];

• fiddle