Does database role permissions take precedence over schema/object level permissions?

All we need is an easy explanation of the problem, so here it is.

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 = '[email protected]'
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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Based on documentation, I would expect the insert through view to fail as well.

Requires UPDATE, INSERT, or DELETE permissions on the target table, depending on the action being performed.

But it works because of a concept called Ownership chaining. Official tutorial is here

When you have access to one object (in this case v.view1) and that object references a securable with the same owner (sch1.table1), then the permissions are not checked at all.

Since both objects have no explicit owner:

by default, schema-contained objects are owned by the schema owner

source

and schema owner can be found here

SELECT
    s.name AS schName, dp.name AS ownerUser
FROM sys.schemas AS s 
JOIN sys.database_principals AS dp
    ON dp.principal_id = s.principal_id
WHERE s.name IN (N'v', N'sch1')

Does database role permissions take precedence over schema/object level permissions?

You can break the ownership chain by changing the owner of the schema v like this:

ALTER AUTHORIZATION ON SCHEMA::v TO Login1

Then you’ll get the DENY you expect.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply