I had an interesting observation on SQL Server security permissions.
My Setup is as below. I am trying to restrict user from updating tables under specific schema.
Problem statement is Why user is allowed to do inserts on table through view (of different schema) even though insert is explicitly denied on schema of table in view definition?
USE master
GO
CREATE LOGIN Login1
WITH password = 'Admin@123'
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE user Login1
FROM LOGIN Login1
GO
CREATE SCHEMA sch1
GO
CREATE SCHEMA V
GO
CREATE TABLE sch1.table1 (
id INT
,name VARCHAR(5)
)
GO
CREATE VIEW v.view1
AS
SELECT *
FROM sch1.table1
GO
CREATE VIEW sch1.view2
AS
SELECT *
FROM sch1.table1
GO
--User can update all tables
ALTER ROLE [db_datawriter] ADD MEMBER [Login1]
GO
--Except tables under this schema
DENY INSERT
ON SCHEMA::[sch1]
TO [Login1]
GO
--Open session with Login1
USE TestDB
GO
--Should not work, does not work.
INSERT sch1.table1
SELECT 1, 'A'
--The INSERT permission was denied on the object 'table1', database 'TestDB', schema 'sch1'.
--Should not work, does not work.
INSERT sch1.view2
SELECT 1, 'A'
--The INSERT permission was denied on the object 'view2', database 'TestDB', schema 'sch1'.
-- Works! - even though write is denied on underlying table?
INSERT v.view1
SELECT 1, 'A'
--(1 row affected)