0

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?

2
  • Are the users added individually or from a domain group? Do all the existent tables and views reside on the same schema (like dbo or some other schema you created)?
    – Ronaldo
    May 16 at 19:26
  • @Ronaldo, All existing data is in dbo schema tables. Users are added individually from a domain (they use Windows Auth, not SQL Server Auth). I am just creating a new schema and trying to restrict access to it but it appears that any user granted db_datareader can read all tables in all schemas.
    – Crescent
    May 16 at 19:57

1 Answer 1

0

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.

1
  • Thank you for the info and for that query. This is the conclusion I came to as well.
    – Crescent
    May 17 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.