编写可重复执行的T-SQL脚本确保登录仅属public角色且无法删表

核心疑问解答：仅属于public服务器角色就能防止删表吗？ #

不能。public是服务器级默认角色，所有登录默认自动属于它，本身仅拥有极少量服务器级权限（比如查看系统视图）。但能否删表取决于该登录对应的数据库用户在数据库中的权限：

• 如果数据库用户被加入db_owner、db_ddladmin等角色，或者直接被授予DROP TABLE权限，依然可以删除表。
• 所以除了重置服务器角色，还需要清理数据库层面的权限。

修复你的脚本错误 #

你执行的ALTER SERVER ROLE public ADD MEMBER HandheldServiceAcct;报错，原因是：

• public是默认服务器角色，所有登录自动属于该角色，不需要手动添加。
• SQL Server语法不允许用ALTER SERVER ROLE给public添加成员，因为它是全员默认角色。

可重复执行的完整脚本 #

以下脚本会：

1. 撤销指定登录在所有非public服务器角色的成员资格
2. 遍历所有用户数据库，确保该登录对应的用户仅属于数据库public角色，且无DROP TABLE权限
3. 可直接用于SQL Server代理作业定期执行

DECLARE @LoginName NVARCHAR(128) = N'HandheldServiceAcct';

-- 步骤1：撤销登录在所有非public服务器角色的成员资格
DECLARE @ServerRoleName NVARCHAR(128);
DECLARE server_roles CURSOR FOR
SELECT name FROM sys.server_principals 
WHERE type = 'R' AND name <> N'public' -- 排除public角色
AND EXISTS (
    SELECT 1 FROM sys.server_role_members rm
    JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
    WHERE rm.role_principal_id = sys.server_principals.principal_id
    AND sp.name = @LoginName
);

OPEN server_roles;
FETCH NEXT FROM server_roles INTO @ServerRoleName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_dropsrvrolemember @LoginName, @ServerRoleName;
    FETCH NEXT FROM server_roles INTO @ServerRoleName;
END;

CLOSE server_roles;
DEALLOCATE server_roles;

-- 步骤2：遍历所有用户数据库，清理对应数据库用户的权限
DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
DECLARE dbs CURSOR FOR
SELECT name FROM sys.databases 
WHERE state = 0 -- 仅在线数据库
AND name NOT IN (N'master', N'model', N'msdb', N'tempdb'); -- 排除系统库

OPEN dbs;
FETCH NEXT FROM dbs INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 撤销用户在当前数据库的非public角色成员资格
    SET @SQL = N'
    USE [' + @DBName + N'];
    DECLARE @DBUserName NVARCHAR(128);
    SELECT @DBUserName = name FROM sys.database_principals 
    WHERE type IN (N''S'', N''U'') AND sid = (SELECT sid FROM sys.server_principals WHERE name = ''' + @LoginName + N''');
    
    IF @DBUserName IS NOT NULL
    BEGIN
        DECLARE @DBRoleName NVARCHAR(128);
        DECLARE db_roles CURSOR FOR
        SELECT name FROM sys.database_principals 
        WHERE type = ''R'' AND name <> N''public''
        AND EXISTS (
            SELECT 1 FROM sys.database_role_members rm
            JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id
            WHERE rm.role_principal_id = sys.database_principals.principal_id
            AND dp.name = @DBUserName
        );

        OPEN db_roles;
        FETCH NEXT FROM db_roles INTO @DBRoleName;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC sp_droprolemember @DBRoleName, @DBUserName;
            FETCH NEXT FROM db_roles INTO @DBRoleName;
        END;

        CLOSE db_roles;
        DEALLOCATE db_roles;

        -- 撤销用户的DROP TABLE权限（如果存在）
        REVOKE DROP TABLE FROM @DBUserName;
        REVOKE DROP ON SCHEMA::dbo FROM @DBUserName;
    END';

    EXEC sp_executesql @SQL;
    FETCH NEXT FROM dbs INTO @DBName;
END;

CLOSE dbs;
DEALLOCATE dbs;

PRINT N'已完成登录 ' + @LoginName + N' 的权限重置，仅保留public服务器角色及数据库public角色，已撤销DROP TABLE权限';
GO

关键说明 #

• 脚本使用游标遍历所有相关角色，确保可重复执行（即使登录当前不在任何非public角色，执行也不会报错）
• 系统库未处理，因为通常业务操作不会在这些库中进行，若需调整可修改dbs游标过滤条件
• 执行脚本需要ALTER ANY LOGIN、ALTER ANY ROLE等服务器级权限，建议用sysadmin角色执行

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