RStudio连接本地SQL Server执行EXECUTE AS语句报权限错误

问题背景 #

• 现有两套SQL Server环境：一套为经多年版本迭代的本地（on-prem）部署实例，初始搭建人员已离职；另一套为该人员离职前不久部署的Azure SQL云实例。
• 正在开发的GUI程序包含用户SQL登录校验环节，参照RStudio官方文档在.Renviron文件中定义系统变量，用户登录时通过db_user变量传递当前登录账号，密码参数可正常传递。
• 初始使用的R端数据库连接代码如下：

db_conn_onprem <- DBI::dbConnect(odbc::odbc(),
  Driver = "SQL Server",
  Server = Sys.getenv("server"),
  Database = Sys.getenv("database"),
  UID = Sys.getenv("db_user"),
  PWD = Sys.getenv("PWD")
)

• 连接身份表现存在差异：Azure SQL实例连接成功后身份显示为dbo@Azure\Azure，本地SQL实例连接成功后身份显示为guest@Server\Server，连接流程在权限校验环节中断，初步判断和权限配置相关。

注：文中所有变量名均已做匿名化处理

故障表现 #

执行枚举数据库列表的查询语句时，本地SQL Server抛出如下报错，相同操作流程在Azure SQL实例上无需额外配置即可正常运行：

Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][SQL Server][SQL Server]Cannot execute as the server principal because the principal "db_user" does not exist, this type of principal cannot be impersonated, or you do not have permission. 
<SQL> 'EXECUTE AS LOGIN = 'db_user' SELECT name FROM master.sys.sysdatabases WHERE dbid > 4 AND HAS_DBACCESS(name) = 1 ORDER BY name ASC'

触发报错的SQL查询代码为：

EXECUTE AS LOGIN = 'db_user' SELECT name 
FROM master.sys.sysdatabases 
WHERE dbid > 4 
AND HAS_DBACCESS(name) = 1 
ORDER BY name ASC

已完成排查动作 #

• 排除R端初始化代码、SQL驱动版本问题：SQL驱动可在R Studio连接上下文菜单中正常拉取数据库名称列表，仅执行上述查询时返回错误。
• 检索公开资料发现，同类场景最常见的报错为：

Cannot execute as the server principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. 

• 已尝试该类报错的多种公开解决方案（包括修复空数据库所有者等操作），均未解决问题。

根因定位 #

该报错和R连接代码、驱动版本无关，核心原因是本地SQL Server的登录名映射异常+EXECUTE AS所需的模拟权限缺失：

1. 本地实例为多年升级迭代的老环境，极易残留旧版本权限配置、出现孤立用户问题，默认权限策略远严于Azure SQL；
2. 连接本地实例后身份显示为guest，直接说明传入的账号未正确映射到服务器级登录名或数据库用户，仅能以访客身份访问受限资源；
3. Azure SQL默认对账号自身模拟的场景做了权限放宽，因此相同代码可直接运行。

分步修复方案 #

按以下顺序逐一验证修复即可：

1. 验证服务器级登录名存在性
   用本地实例的sysadmin权限账号登录，执行以下语句确认传入的db_user对应账号是否存在于服务器级主体列表：

   SELECT name, principal_id, type_desc, is_disabled 
   FROM sys.server_principals 
   WHERE name = '实际传入的db_user账号值'
   

   • 无返回结果：说明该账号未在本地实例创建服务器级登录名，之前能拉取库列表是guest账号的默认元数据访问权限生效，直接创建同名登录名、配置匹配密码即可；
   • 返回结果中is_disabled=1：先执行ALTER LOGIN [账号名] ENABLE启用登录名。
2. 授予模拟权限
   EXECUTE AS LOGIN要求执行语句的当前安全上下文，必须对被模拟的登录名持有IMPERSONATE权限，guest账号默认无该权限。用sysadmin账号执行以下语句授权：

   -- 如果用账号自身连接、模拟自身，直接授权账号自身的模拟权限即可
   GRANT IMPERSONATE ON LOGIN::[实际使用的连接账号名] TO [实际使用的连接账号名];
   

   如果业务逻辑是用A账号连接、模拟B账号，将语句中TO后的账号替换为A即可。
3. 修复登录名-数据库用户映射
   老版本升级的SQL Server常出现孤立用户问题：数据库内存在对应用户，但用户SID和服务器级登录名SID不匹配，连接后自动降级为guest身份。切到目标连接库执行以下语句修复映射：

   USE [连接的目标数据库名];
   -- 修复SID映射
   ALTER USER [实际使用的账号名] WITH LOGIN = [实际使用的服务器级登录名];
   -- 授予库列表查看权限
   GRANT VIEW ANY DATABASE TO [实际使用的服务器级登录名];
   

4. 冗余逻辑优化（推荐）
   现有查询逻辑中EXECUTE AS LOGIN属于冗余操作：连接本身就是用db_user账号建立的，安全上下文本来就是该账号，直接去掉模拟语句即可从根源规避权限问题，修改后的查询如下：

   SELECT name 
   FROM master.sys.sysdatabases 
   WHERE dbid > 4 
   AND HAS_DBACCESS(name) = 1 
   ORDER BY name ASC
   

注意：修复完成后建议重启R的数据库连接，避免旧连接的权限缓存导致验证结果不准。

内容的提问来源于stack exchange，提问作者Lane O'Brien