创建可修改用户映射的SQL角色时遇到问题

错误原因很明确：你在master库中创建的UserMappingEditor是数据库级角色，仅存在于master库内，其他用户数据库中并没有这个角色，所以执行跨库授权语句时会提示找不到该主体。

要实现一个能修改任意数据库用户映射的自定义角色，正确的做法是创建服务器级角色，再在每个数据库中为该服务器角色创建对应的数据库用户并授权。具体步骤如下：

修正后的步骤 #

步骤1：创建服务器级角色 #

USE master;
GO

CREATE SERVER ROLE UserMappingEditor;

步骤2：授予服务器级基础权限（可选，用于查看所有数据库） #

USE master;
GO

GRANT VIEW ANY DATABASE TO UserMappingEditor;

步骤3：在所有在线数据库中创建映射用户并授权 #

DECLARE @DatabaseName sysname;
DECLARE @SQL NVARCHAR(MAX);

DECLARE DatabaseCursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state = 0; -- 排除离线数据库

OPEN DatabaseCursor;

FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 先检查数据库中是否已存在对应用户，不存在则创建，再授予权限
    SET @SQL = N'USE [' + @DatabaseName + N']; 
                 IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''UserMappingEditor'' AND type = ''S'')
                 BEGIN
                     CREATE USER UserMappingEditor FOR SERVER ROLE UserMappingEditor;
                 END;
                 GRANT ALTER ANY USER TO UserMappingEditor;';
    
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;
END

CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;

补充说明 #

• 服务器级角色是实例级的主体，可以跨数据库统一管理权限；而数据库角色仅作用于创建它的单个数据库。
• 使用sp_executesql替代直接拼接字符串执行，能避免潜在的SQL注入风险，同时处理含特殊字符的数据库名场景。

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