限制某些列的更新。只允許儲存過程更新那些列

我有敏感的價格列，我希望只通過儲存過程來更新這些列。如果不使用旨在更新它的儲存過程，我希望所有程式碼或手動嘗試更改這些價格列中的值都失敗。

我正在考慮使用觸發器和令牌表來實現這一點。我正在考慮的想法是有一個令牌表。儲存過程必須首先在令牌表中插入值。然後更新價格列。更新觸發器將檢查令牌表中是否存在更新行的令牌。如果找到，它將繼續。如果找不到token，就會拋出異常，使更新事務失敗。

有沒有更好/更好的方法來實施這個限制？

SQL Server 允許列級權限。舉個例子：

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;

-- prevent your web app user from updating that column directly:

DENY UPDATE ON dbo.YourTable(Price) TO WebApplicationUserName;
GO

-- create a stored procedure while logged in as sysadmin:

CREATE PROCEDURE dbo.UpdateYourTable
 @ProductID INT,
 @Price DECIMAL(10,2)
WITH EXECUTE AS OWNER
AS
BEGIN
 SET NOCOUNT ON;

 UPDATE dbo.YourTable 
   SET Price = @Price
   WHERE ProductID = @ProductID;
END
GO

-- grant explicit access only to that stored procedure to the web app user:

GRANT EXEC ON dbo.UpdateYourTable TO WebApplicationUserName;

引用自：https://dba.stackexchange.com/questions/24734