如何在SQL Server中禁用除应用创建用户外的所有登录账户？

一、禁用除目标用户外的所有登录账户 #

注意：不要直接禁用所有系统级登录（如sa、BUILTIN\Administrators），建议保留一个应急管理员账户，避免后续无法维护实例。如果是交付给客户的项目，可将应急账户的密码交由客户妥善保管。

具体操作步骤 #

1. 确认应用用户登录已创建并配置完成
   确保应用代码创建的登录（假设名为AppSpecificLogin）已存在，且已映射到目标数据库的用户，拥有业务所需的最小权限（避免过度授权）。

2. 批量禁用非必要登录账户
   使用T-SQL脚本禁用除指定登录外的其他账户：

-- SQL Server 2005及以上版本推荐使用ALTER LOGIN
DECLARE @LoginName NVARCHAR(128)
DECLARE LoginCursor CURSOR FOR
SELECT name FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')  -- S=SQL登录，U=Windows用户，G=Windows组
  AND name NOT IN ('AppSpecificLogin', 'sa', 'NT SERVICE\SQLSERVERAGENT')  -- 保留必要账户，按需调整
  AND is_disabled = 0

OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @LoginName

WHILE @@FETCH_STATUS = 0
BEGIN
    ALTER LOGIN @LoginName DISABLE;
    FETCH NEXT FROM LoginCursor INTO @LoginName
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

3. 锁死目标数据库的访问权限
   即使禁用了登录，也要确保目标数据库只有应用用户能访问：

USE YourTargetDB;  -- 替换为你的数据库名
GO

-- 移除数据库内非必要用户
DECLARE @UserName NVARCHAR(128)
DECLARE UserCursor CURSOR FOR
SELECT name FROM sys.database_principals
WHERE type IN ('S', 'U', 'G')
  AND name NOT IN ('AppSpecificUser', 'dbo')  -- 保留应用用户和dbo（按需调整）
  AND name != 'guest'

OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @UserName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 移除用户的角色权限
    EXEC sp_droprolemember 'db_owner', @UserName;
    EXEC sp_droprolemember 'db_datareader', @UserName;
    EXEC sp_droprolemember 'db_datawriter', @UserName;
    -- 删除用户
    DROP USER IF EXISTS @UserName;
    FETCH NEXT FROM UserCursor INTO @UserName
END

CLOSE UserCursor
DEALLOCATE UserCursor

-- 禁用guest用户
ALTER USER guest DISABLE;

二、关于禁用.\sqlexpress实例的问题 #

直接禁用实例（停止服务、隐藏实例）不是可靠方案：停止服务会导致应用无法访问；隐藏实例只能防浏览，知道实例名的用户仍可连接。更稳妥的做法是：

• 固定Express实例端口：在SQL Server配置管理器中，将Express实例的TCP/IP端口设为固定值（如1433），避免动态端口带来的访问不确定性。
• 配置防火墙规则：仅允许应用所在服务器的IP访问SQL Server端口，阻断其他来源的连接请求。
• 限制实例登录权限：通过服务器级权限严格控制谁能连接实例：

  -- 禁止所有未明确授权的登录连接实例
  DENY CONNECT SQL TO [PUBLIC];
  -- 仅允许应用用户和应急管理员连接
  GRANT CONNECT SQL TO [AppSpecificLogin];
  GRANT CONNECT SQL TO [EmergencyAdminLogin];  -- 替换为你的应急管理员账户
  

三、关键注意事项 #

• 务必保留应急管理员登录，否则后续应用故障或维护时将无法访问实例。
• 操作前备份master、msdb和业务数据库，防止操作失误导致数据或实例不可用。
• 测试验证：用非授权账户尝试连接，确认无法访问；用应用账户测试，确保能正常执行业务操作。

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