如何在AWS RDS多MSSQL数据库中重置用户并配置正确权限

在AWS RDS的MSSQL实例中，多个数据库均绑定了登录账号sysuser对应的同名用户。当程序安装新数据库时，会导致其他数据库的连接与权限异常。已知无需操作的系统库为tempdb、rdsadmin、msdb、model和master，需要批量修复所有其他数据库的sysuser用户权限：删除现有用户，重新映射登录账号，并赋予db_datareader、db_datawriter、db_owner角色。

尝试过用sp_MSforeachdb但效果不稳定（受登录用户默认数据库影响），也尝试遍历数据库但未找到正确实现方式，相关尝试脚本如下：

初步遍历脚本：

DECLARE @tables VARCHAR(1000) OUTPUT
use [master]
@tables = select name from master.dbo.sysdatabases where NOT name='rdsadmin' AND NOT name='master' AND NOT name='msdb' AND NOT name='model' AND NOT name='tempdb'

?? for table in tables ??
BEGIN
    use [table]

    -- prevent dropping if it owns the schema
    ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo
    DROP USER sysuser
    CREATE USER sysuser from login sysuser;
    ALTER ROLE [db_datareader] ADD MEMBER sysuser;
    ALTER ROLE [db_datawriter] ADD MEMBER sysuser;
    ALTER ROLE [db_owner] ADD MEMBER sysuser;
    GO
END

sp_MSforeachdb尝试脚本：

EXEC sp_MSforeachdb
@command1='IF ''?''
NOT IN (''rdsadmin'', ''master'', ''msdb'', ''model'', ''tempdb'')
BEGIN
    ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo
    IF EXISTS(select 1 from sys.database_principals where name=''sysuser'')
        DROP USER sysuser
    CREATE USER sysuser from login sysuser;
    ALTER ROLE [db_datareader] ADD MEMBER sysuser;
    ALTER ROLE [db_datawriter] ADD MEMBER sysuser;
    ALTER ROLE [db_owner] ADD MEMBER sysuser;
END'

寻求更可靠的批量处理方案。

以下脚本通过生成并执行动态SQL，避免sp_MSforeachdb的稳定性问题，同时确保操作的安全性与可追溯性：

DECLARE @SQL NVARCHAR(MAX) = N'';

-- 生成所有目标数据库的操作脚本
SELECT @SQL += N'
USE [' + QUOTENAME(d.name) + N'];
BEGIN TRY
    -- 转移db_owner架构所有权，避免删除用户失败
    ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
    -- 如果用户存在则删除
    IF EXISTS(SELECT 1 FROM sys.database_principals WHERE name = N''sysuser'')
    BEGIN
        DROP USER sysuser;
    END
    -- 重新创建用户并映射登录账号
    CREATE USER sysuser FOR LOGIN sysuser;
    -- 赋予指定角色权限
    ALTER ROLE db_datareader ADD MEMBER sysuser;
    ALTER ROLE db_datawriter ADD MEMBER sysuser;
    ALTER ROLE db_owner ADD MEMBER sysuser;
    PRINT N''处理完成：' + d.name + N''';
END TRY
BEGIN CATCH
    PRINT N''处理失败：' + d.name + N' - '' + ERROR_MESSAGE();
END CATCH
'
FROM sys.databases d
WHERE d.name NOT IN (N'tempdb', N'rdsadmin', N'msdb', N'model', N'master')
-- 仅处理在线状态的数据库
AND d.state_desc = N'ONLINE';

-- 执行生成的脚本
EXEC sp_executesql @SQL;

脚本说明 #

• 使用官方推荐的sys.databases系统视图替代过时的master.dbo.sysdatabases
• 通过QUOTENAME处理数据库名称，避免特殊字符或空格导致的语法错误
• 加入TRY...CATCH块捕获异常，输出处理状态，便于排查问题
• 仅对在线数据库执行操作，跳过离线/恢复中的数据库
• 先转移db_owner架构所有权，防止因用户持有架构导致删除失败

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