I want to create some custom database roles, which only allow their members to SELECT all tables except some specific tables with some columns DENY
this is my attempt :
CREATE ROLE CustomDatabaseRole01
GO
DENY SELECT ON OBJECT::dbo.Table1(c1,c2) TO
CustomDatabaseRole01
GO
DENY SELECT ON OBJECT::dbo.Table2(c3,c4) TO
CustomDatabaseRole01
GO
ALTER ROLE db_datareader ADD MEMBER UserTest01
GO
ALTER ROLE CustomDataBaseRole01 ADD MEMBER UserTest01
GO
this does not work, you can select any column in any table