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):
- Create new schema called 'secret_schema'
- Create 2 new roles: 'secret_schema_owner' (owner = dbo) and 'secret_schema_reader' (owner = 'secret_schema_owner')
- Set owner of 'secret_schema' to 'secret_schema_owner' role
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?