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

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

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?

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

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.

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