Restrict access to a new schema to a specific role despite users having db_datareader?

Question

I'm working in a SQL Server 2016 database that is not of my design nor can I fundamentally change the security structure. I know it is likely trash/worst practice but I have to play the hand I am dealt at the moment. I'm trying to restrict access to a schema to only members of a role, but everyone seems to have db_datareader membership. Here are the details:

We will be ETL'ing some new data into their own tables within this existing database. All these "new" tables need to be access restricted so only certain users can view. Here is my thinking (using fake names):

1. Create new schema called 'secret_schema'
2. Create 2 new roles: 'secret_schema_owner' (owner = dbo) and 'secret_schema_reader' (owner = 'secret_schema_owner')
3. Set owner of 'secret_schema' to 'secret_schema_owner' role
4. GRANT SELECT ON SCHEMA::secret_schema TO secret_schema_reader

The goal here is that only members of the 'secret_schema_reader' role will be able to view the data in tables within the 'secret_schema' schema. The problem is that the prior managers of this database just granted users 'db_datareader' to the database and that seems to trump my attempted role security.

Do I have any options besides a total overhaul of the security/permissions structure?

Answer 1

Members of db_datareader can in fact read any table/view in any schema. Which is why it's often advised not to use this role if you need finegrained access control.

You could create a new 'common_read' role, and just assign your users/groups to that one - and make a more restrictive grant (ie. don't grant access to 'secret_schema')

To copy role-assignments:

-- execute this, copy the output to query window and execute
select concat('exec sp_addrole_member ''common_read'', ',rp.name,''';')
from sys.database_role_members rm
join sys.database_principals dp on (rm.role_principal_id = dp.principal_id)
join sys.database_principals rp ON (rm.member_principal_id = rp.principal_id)
where dp.name='db_datareader'

In a pinch, you can always DENY access to a schema. This will trump any GRANT, so create a role, assign the members from db_datareader and DENY access to 'secret_schema'. But it's sort of a backwards way of doing it as it will require managing role memberships twice.