如何通过T-SQL存储过程将指定用户从所有数据库角色移除

整体实现逻辑很直接：先通过系统视图查询出目标用户所属的所有数据库角色，再遍历拼接移除成员的动态SQL，批量执行即可。实现时需要重点处理特殊字符转义、存在性校验的问题，避免触发语法报错。

完整存储过程代码 #

CREATE OR ALTER PROCEDURE dbo.sp_RemoveUserFromAllDatabaseRoles
    @TargetUserName SYSNAME -- 入参：需要处理的目标数据库用户名
AS
BEGIN
    SET NOCOUNT ON;

    -- 校验目标用户是否在当前数据库存在
    IF NOT EXISTS (
        SELECT 1 
        FROM sys.database_principals 
        WHERE name = @TargetUserName
    )
    BEGIN
        THROW 50001, '指定的数据库用户不存在于当前操作的数据库中', 1;
        RETURN;
    END

    -- 遍历查询到的角色列表，拼接动态移除语句
    DECLARE @ExecSQL NVARCHAR(MAX) = N'';
    SELECT @ExecSQL = @ExecSQL + 
        N'ALTER ROLE ' + QUOTENAME(role_name.name) + 
        N' DROP MEMBER ' + QUOTENAME(@TargetUserName) + N';' + CHAR(10)
    FROM sys.database_role_members role_members
    INNER JOIN sys.database_principals role_name 
        ON role_name.principal_id = role_members.role_principal_id
    INNER JOIN sys.database_principals member_name 
        ON member_name.principal_id = role_members.member_principal_id
    WHERE member_name.name = @TargetUserName;

    -- 执行拼接完成的移除逻辑
    IF @ExecSQL <> N''
    BEGIN
        EXEC sp_executesql @ExecSQL;
        PRINT N'已完成操作：用户 ' + @TargetUserName + N' 已从所有所属数据库角色中移除';
    END
    ELSE
    BEGIN
        PRINT N'当前用户不属于任何数据库角色，无需执行移除操作';
    END
END

使用方式 #

如果要将名为myUser的用户从其所有所属数据库角色中移除，直接调用存储过程并传入用户名即可：

EXEC dbo.sp_RemoveUserFromAllDatabaseRoles @TargetUserName = 'myUser';

关键设计说明 #

• 所有从系统视图获取的角色名、传入的用户名都用QUOTENAME做转义包裹，即使角色名包含空格、特殊符号也不会触发语法错误，同时可抵御简单的SQL注入风险
• 增加前置校验逻辑，传入不存在的用户名时直接抛出明确提示，避免无意义的执行报错
• 自动检测用户的角色归属情况，如果用户本身不属于任何角色，会直接给出提示，不会执行空SQL
• 入参采用系统匹配的SYSNAME类型，和系统视图的名称字段类型完全对齐，不会出现类型截断问题
• 采用CREATE OR ALTER语法，兼容SQL Server 2016及以上版本，存储过程首次创建、后续迭代修改都可直接执行，不需要先手动删除旧版本

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