I have restricted select access at table level in sql server. But users are able to access views which are created top of that table

Question

I have restricted select access at table level for 1 single table 'A'in sql server(2016). But users are able to access views which are created top of that table 'A'.

when users run select statement on table 'A'(select * from table 'A')users are getting 'select permission is denied on object table 'A' ' message. when users run select statement on view (select * from view 'A') users are able to access all data.

Users are able to create new views by writing select statement on table 'A'.

How can i restrict users to access table 'A' from views as well.

Answer 1

This is expected behavior due to ownership chaining. Permissions on tables referenced by the view are not checked as long as the view and tables have the same owner (AUTHORIZATION).

To break the ownership chain, you could either move the table to a different schema (owned by a different user than the view schema) or change to owner of the table. Examples below.

--move table to different schema
CREATE USER RestricedTablesOwner WITHOUT LOGIN;
GO
CREATE SCHEMA RestricedTables AUTHORIZATION RestricedTablesOwner;
GO
ALTER SCHEMA RestricedTables TRANSFER dbo.A;
GO

--change table to different owner, retaining same schema
ALTER AUTHORIZATION ON OBJECT::dbo.A TO RestricedTablesOwner;