Azure AD成员登录SQL Server无法查看全库且遇18456登录失败问题

问题背景 #

使用Azure AD成员登录SSMS时，希望无需指定具体数据库即可查看所有数据库，但创建Azure AD组[SQL-READ]作为master库登录名，并在master及另外两个库创建同名用户后，登录出现“Login failed for user "xxxxxxx" 18456”错误，仅指定具体数据库才能连接。

排查与解决步骤 #

• 检查登录的默认数据库设置
  未指定数据库登录时，SQL Server会使用登录账号的默认数据库。若默认数据库不是用户有权限访问的库，就会触发18456错误。执行以下语句将默认数据库改为master（需确保用户已在master库有对应权限）：

  ALTER LOGIN [SQL-READ] WITH DEFAULT_DATABASE = master;
  GO
  

• 授予VIEW ANY DATABASE权限
  要让用户能看到所有数据库，需给master库的登录账号授予VIEW ANY DATABASE权限，执行：

  USE master;
  GRANT VIEW ANY DATABASE TO [SQL-READ];
  GO
  

  该权限允许登录账号在SSMS“数据库”节点下查看所有库，访问具体库仍需对应库的用户权限。

• 验证Azure AD组成员身份同步
  确认登录的Azure AD用户确实属于[SQL-READ]组，且Azure AD与SQL Server的身份同步已完成。可在master库执行以下语句检查登录及用户信息：

  SELECT * FROM sys.server_principals WHERE name = 'SQL-READ';
  SELECT * FROM sys.database_principals WHERE name = 'SQL-READ';
  

  同时让用户退出并重新登录SSMS，确保身份令牌更新。

• 检查数据库用户关联正确性
  确保每个目标数据库中的[SQL-READ]用户已正确关联到master库的同名登录，执行：

  USE [目标数据库名];
  SELECT s.name AS LoginName, d.name AS UserName
  FROM sys.database_principals d
  JOIN sys.server_principals s ON d.sid = s.sid
  WHERE d.name = 'SQL-READ';
  

  若关联错误，删除现有用户重新创建：

  USE [目标数据库名];
  DROP USER [SQL-READ];
  CREATE USER [SQL-READ] FROM LOGIN [SQL-READ];
  GO
  

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