自建应用连接SQL Server理论疑问：登录名/数据库用户选择及sp_setapprole原理

连接时需要SQL Server登录名还是仅数据库用户？ #

首先得明确SQL Server的两个核心安全层级：

• 服务器级别：登录名（Login）是用来验证你能不能连接到SQL Server实例的核心凭证，没有登录名的话，连服务器这一步都无法通过验证。
• 数据库级别：数据库用户（User）是登录名在具体数据库里的权限映射，它只负责控制你在这个数据库内的初始操作权限，但没法单独用来完成服务器连接。

所以你连接的时候必须要有SQL Server登录名，这个登录名需要提前映射到目标数据库里的一个数据库用户（这个用户得被授予执行sp_setapprole的权限）。仅用数据库用户是没法直接连接到SQL Server实例的，因为它没有服务器级别的验证资格。

sp_setapprole的运作机制 #

应用角色（App Role）是SQL Server里一种特殊的数据库角色，它的核心特点是默认没有登录名关联，必须通过已授权的数据库用户来激活，而sp_setapprole就是激活它的关键存储过程，具体运作流程是这样的：

1. 初始连接与基础权限：你先用合法的登录名（已映射到目标数据库用户）连接到SQL Server和目标数据库，此时你的会话拥有这个数据库用户的基础权限。
2. 触发角色切换：调用存储过程EXEC sp_setapprole @rolename = '你的应用角色名', @password = '应用角色密码';（如果需要后续主动切回原权限，可以加上@fCreateCookie参数生成凭证cookie）。
3. 安全上下文替换：执行成功后，当前会话的安全上下文会临时替换为应用角色的权限——原来数据库用户的权限会被暂时屏蔽，你的会话现在拥有应用角色被赋予的所有数据库操作权限。
4. 上下文恢复：直到你的数据库会话断开，这个临时的安全上下文才会自动恢复。如果执行时生成了cookie，还可以调用sp_unsetapprole传入cookie，提前切换回原来的数据库用户权限。

这个机制的核心价值是最小权限原则：你不需要给连接用的数据库用户太多业务权限，只需要给它执行sp_setapprole的权限就行，真正的业务操作权限都集中赋予应用角色，这样既保证了连接环节的安全性，又能让应用以合适的权限完成业务操作，大幅降低权限泄露的风险。

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