SQL Server非sysadmin角色用户无法连接问题求助

让我一步步帮你解决这个问题，先搞定Public角色的权限恢复，再优化用户配置，确保非sysadmin用户能正常连接并只读访问数据库：

一、恢复Public角色的默认权限 #

你查到多个权限被设置为DENY，这些DENY会直接覆盖用户的显式权限，导致连不上服务器。我们需要撤销这些DENY，让Public回到默认状态：

-- 撤销Public角色上的所有DENY权限，恢复默认配置
REVOKE DENY Administer Bulk Operations TO PUBLIC;
REVOKE DENY Authenticate Server TO PUBLIC;
REVOKE DENY Connect any database TO PUBLIC;
REVOKE DENY Control Server TO PUBLIC;
REVOKE DENY View Any Definition TO PUBLIC;
REVOKE DENY View Any Database TO PUBLIC;
REVOKE DENY View Any Server State TO PUBLIC;

为什么要这么做？ #

• Authenticate Server是用户登录验证的核心权限，DENY后非sysadmin用户根本无法通过身份验证，这是你当前连接失败的关键原因。
• Connect any database默认不是Public的权限，保持“无授予也无拒绝”的状态即可，DENY会导致即使你给了用户某个数据库的权限，也无法连接该库。
• 其他几个权限（如View Any Definition、View Any Server State）默认Public是有VIEW权限的，撤销DENY后回归默认就好，不需要额外授予。

二、正确配置只读用户的权限（替代旧脚本） #

你之前用的sp_addlogin等是旧版存储过程，推荐用更现代的CREATE LOGIN/CREATE USER语法，同时注意不要在master库授予db_datareader（一般业务数据不在master），正确步骤如下：

1. 创建服务器登录名

-- 创建服务器级登录，指定默认数据库（建议设为业务数据库而非master）
CREATE LOGIN [user] WITH PASSWORD = 'P@55w0rd123!', DEFAULT_DATABASE = [YourBusinessDatabase];

2. 在目标业务数据库创建用户并授予只读权限

-- 切换到你的业务数据库（替换成实际库名）
USE [YourBusinessDatabase];
-- 创建数据库用户映射到服务器登录
CREATE USER [user] FOR LOGIN [user];
-- 授予只读权限（两种写法，选其一即可，后者是SQL Server 2012+推荐语法）
EXEC sp_addrolemember 'db_datareader', [user];
-- ALTER ROLE db_datareader ADD MEMBER [user];

3. 确保服务器连接权限正常
   你已经给Public授予了端点连接权限，这一步没问题。如果用户还是无法连接，检查是否需要单独授予登录权限：

-- 给单个用户授予服务器连接权限（如果Public的Connect SQL被修改过）
GRANT CONNECT SQL TO [user];
-- 或者让所有登录都能连接（恢复Public默认）
-- GRANT CONNECT SQL TO PUBLIC;

三、关键注意事项 #

• 不要随意修改Public角色的权限！它是所有服务器登录的默认角色，修改会影响所有用户。如果需要限制个别用户，直接针对该用户设置DENY，而非修改Public。
• 避免给普通用户授予服务器级的高权限，db_datareader是数据库级权限，刚好满足只读需求，不会让用户执行T-SQL修改操作。
• 测试时用该用户登录SSMS，尝试执行INSERT/UPDATE等操作，确认无法执行，同时能正常读取表数据，验证配置生效。

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