如何用T-SQL从表变量动态创建用户并授予权限？

你的需求完全可以实现，但提供的示例代码存在几处语法和逻辑错误，无法直接运行。以下是修正后的实现方案：

原代码问题说明 #

• 错误使用COUNT(@MyList)，正确写法应为SELECT COUNT(*) FROM @MyList
• WHILE循环未用BEGIN/END包裹多条语句，导致仅第一条语句会执行循环
• CREATE USER和GRANT属于DDL语句，无法直接通过变量指定对象名，必须使用动态SQL执行

方案1：带序号的循环遍历 #

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

DECLARE @cnt INT = 1;
DECLARE @total INT;
DECLARE @userName NVARCHAR(150);

-- 获取表变量总记录数
SELECT @total = COUNT(*) FROM @MyList;

WHILE @cnt <= @total
BEGIN
    -- 获取当前循环的用户名
    SELECT @userName = Value FROM @MyList WHERE ID = @cnt;
    
    -- 动态创建外部用户
    EXEC sp_executesql N'CREATE USER [@UserName] FROM EXTERNAL PROVIDER;',
        N'@UserName NVARCHAR(150)',
        @UserName = @userName;
    
    -- 动态授予权限（将XXXX替换为实际需要的权限，如SELECT ON SCHEMA::dbo）
    EXEC sp_executesql N'GRANT XXXX TO [@UserName];',
        N'@UserName NVARCHAR(150)',
        @UserName = @userName;
    
    SET @cnt = @cnt + 1;
END

方案2：游标遍历（更简洁的遍历方式） #

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

DECLARE @userName NVARCHAR(150);
-- 声明游标遍历表变量中的用户名
DECLARE userCursor CURSOR FOR SELECT Value FROM @MyList;

OPEN userCursor;
FETCH NEXT FROM userCursor INTO @userName;

-- 循环处理每一个用户名
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql N'CREATE USER [@UserName] FROM EXTERNAL PROVIDER;',
        N'@UserName NVARCHAR(150)',
        @UserName = @userName;
    
    EXEC sp_executesql N'GRANT XXXX TO [@UserName];',
        N'@UserName NVARCHAR(150)',
        @UserName = @userName;
    
    FETCH NEXT FROM userCursor INTO @userName;
END

-- 关闭并释放游标
CLOSE userCursor;
DEALLOCATE userCursor;

注意事项 #

• 替换代码中的XXXX为实际需要授予的权限，例如SELECT ON SCHEMA::dbo、EXECUTE ON OBJECT::dbo.YourProcedure等
• 执行脚本的账号需要具备创建用户和授予权限的对应权限（如ALTER ANY USER、GRANT ANY OBJECT PERMISSION等）
• 使用sp_executesql带参数的方式可自动处理用户名含特殊字符的情况，避免SQL注入风险

内容的提问来源于stack exchange，提问作者Potis23