SQL Server 2012授予列级INSERT/UPDATE权限报1020错误解决问询

报错原因 #

你遇到的Msg 1020错误核心原因是：INSERT属于SQL Server的实体级（表级）权限，不支持指定列粒度授予，仅SELECT、UPDATE、REFERENCES三类权限支持列级授权。你原语句中将INSERT和UPDATE合并书写、同时指定了列，就会触发该权限规则报错。

列级SELECT、UPDATE权限的正确授予方法 #

将INSERT权限和支持列级的权限分开书写即可：

-- 单独授予表级INSERT权限（无列级配置选项）
GRANT INSERT ON [db].[dbo].[table] TO [user];

-- 授予指定列的SELECT权限
GRANT SELECT ON [db].[dbo].[table]([column1], [column2]) TO [user];

-- 授予指定列的UPDATE权限
GRANT UPDATE ON [db].[dbo].[table]([column1], [column2]) TO [user];

如需限制INSERT仅能写入指定列的替代方案 #

如果你的业务需求是限制用户INSERT时只能给指定列赋值，无法通过原生INSERT权限实现，可使用以下两种常见方案：

• 方案1：创建仅包含允许插入列的视图，给用户授予视图的INSERT权限，禁止用户直接访问基表

  -- 创建仅包含允许插入列的视图
  CREATE VIEW [db].[dbo].[v_table_limit_insert]
  AS
  SELECT [column1], [column2] FROM [db].[dbo].[table]; -- 此处仅列出允许用户插入的列
  
  -- 给用户授予视图的INSERT权限
  GRANT INSERT ON [db].[dbo].[v_table_limit_insert] TO [user];
  

• 方案2：编写专用INSERT存储过程，在存储过程逻辑中限制仅允许写入指定列，给用户授予该存储过程的EXECUTE权限即可。

内容的提问来源于stack exchange，提问作者Ravi Teja Gadi