使用动态SQL执行UPDATE时遇权限错误，常规语句可正常运行

这是因为SQL Server的所有权链机制对动态SQL不生效。

当执行非动态SQL的存储过程时，只要存储过程的所有者对目标表具备UPDATE权限，就算调用者没有表的直接权限也能正常执行——这是所有权链的作用。但动态SQL（比如通过sp_executesql执行的字符串）会跳出存储过程的上下文，直接以当前执行的用户身份（也就是HandheldServiceAcct）去访问表，而该用户没有TableName的UPDATE权限，因此触发权限错误。

可以通过以下几种方式解决该问题：

1. 直接给用户分配表的UPDATE权限 #

如果业务场景允许，最简单的方式就是直接给HandheldServiceAcct授权：

GRANT UPDATE ON dbo.TableName TO HandheldServiceAcct;

但这种方式会直接赋予用户表的修改权限，不符合最小权限原则的场景需谨慎使用。

2. 给存储过程添加EXECUTE AS子句 #

修改存储过程，让它以拥有表权限的身份执行动态SQL，比如以存储过程所有者或dbo身份：

ALTER PROCEDURE [dbo].[spHandheldTest] 
WITH EXECUTE AS OWNER -- 也可以指定具体用户，比如EXECUTE AS 'dbo'
AS
BEGIN
    DECLARE @sql nvarchar(4000) = 'UPDATE [TableName] SET [Order Quantity] = 1 WHERE [ID] = 1' 
    EXEC sp_executesql @sql 
END

注意：使用EXECUTE AS OWNER需要存储过程的所有者对目标表有足够权限，同时调用者需要具备IMPERSONATE该所有者的权限（若所有者为dbo，通常默认允许）。

3. 使用证书签名实现最小权限（推荐） #

如果不想直接给用户表权限，也不想引入EXECUTE AS的上下文问题，可以通过证书签名存储过程来赋予其必要权限：

1. 创建证书：

CREATE CERTIFICATE ProcSignCert
ENCRYPTION BY PASSWORD = 'StrongPassword123!'
WITH SUBJECT = 'Certificate for spHandheldTest',
EXPIRY_DATE = '2030-12-31';

2. 创建关联证书的用户并赋予表权限：

CREATE USER ProcSignUser FROM CERTIFICATE ProcSignCert;
GRANT UPDATE ON dbo.TableName TO ProcSignUser;

3. 给存储过程添加证书签名：

ADD SIGNATURE TO [dbo].[spHandheldTest]
BY CERTIFICATE ProcSignCert
WITH PASSWORD = 'StrongPassword123!';

4. 备份证书（可选，用于后续恢复）：

BACKUP CERTIFICATE ProcSignCert TO FILE = 'C:\SQLCerts\ProcSignCert.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLCerts\ProcSignCert.pvk',
ENCRYPTION BY PASSWORD = 'StrongPassword123!');

这种方式能在不暴露表权限给终端用户的前提下，让存储过程拥有执行UPDATE的权限，符合最小权限安全原则。

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