SQL Azure连接master时HAS_DBACCESS返回NULL的解决方案咨询

问题背景 #

在本地SQL Server或托管实例的master库执行select HAS_DBACCESS(N'MyRep')，会返回1（有权限）或0（无权限）；但在SQL Azure的master库执行该语句时返回NULL，必须切换到目标库才能得到正确结果，陷入逻辑困境。

可行解决方案 #

可以通过查询master库中的系统视图关联目标数据库的权限信息，以下是两种有效实现方式：

方法1：基于登录名与数据库用户的关联判断 #

SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM sys.databases d
            JOIN sys.database_principals dp ON d.database_id = dp.database_id
            JOIN sys.server_principals sp ON dp.sid = sp.sid
            WHERE d.name = N'MyRep'
              AND sp.name = SUSER_SNAME()
              AND dp.type IN ('S', 'U', 'G') -- 覆盖SQL登录、Azure AD用户/组
              AND dp.is_disabled = 0
        ) THEN 1
        ELSE 0
    END AS HasDbAccess;

逻辑说明：在master库中关联数据库、数据库主体和服务器主体，检查当前登录名是否在目标数据库中存在对应的启用状态的用户/组，以此判断访问权限。

方法2：补充服务器级权限判断 #

如果用户拥有服务器级CONTROL SERVER权限（对应sysadmin角色），默认对所有数据库有访问权，可加入该判断优化逻辑：

SELECT 
    CASE 
        WHEN IS_SRVROLEMEMBER(N'sysadmin') = 1 THEN 1
        WHEN EXISTS (
            SELECT 1 
            FROM sys.databases d
            JOIN sys.database_principals dp ON d.database_id = dp.database_id
            JOIN sys.server_principals sp ON dp.sid = sp.sid
            WHERE d.name = N'MyRep'
              AND sp.name = SUSER_SNAME()
              AND dp.type IN ('S', 'U', 'G')
              AND dp.is_disabled = 0
        ) THEN 1
        ELSE 0
    END AS HasDbAccess;

补充说明：SQL Azure的master库无法直接读取其他数据库的sys.database_permissions，但通过数据库主体的存在性可间接判断权限——拥有访问权限的用户，必然在目标数据库中存在对应主体（角色继承的权限场景，可通过扩展角色关联查询补充，基础场景下上述语句已覆盖大部分需求）。

注意事项 #

• 上述语句仅适用于SQL Azure环境，本地SQL Server或托管实例可继续使用HAS_DBACCESS函数。
• 目标数据库属于弹性池时，语句依然有效，因为sys.databases视图包含所有用户数据库。

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