Answered by:

How to deny WinGroups having db_datareader access from accessing a new schema

Question

I have a scenario wherein I need to create a new schema and allow access on it, to only one particular WinGroup, while preventing other WinGroups from accessing the new schema.

The thing is that, all those WinGroups in question are in db_datareader role in the database and they should continue to access other tables in the database, with the exception
of the schema in question.

One solution that I could think of is using a script as below:

Step 1: CREATE SCHEMA Schema_A AUTHORIZATION dbo

Step 2: Grant access to WinGroup_A

GRANT SELECT ON SCHEMA::Schema_A to WinGroup_A

Step 3: Deny access to other WinGroups

DENY SELECT ON SCHEMA::Schema_A to ADGroup_B

DENY SELECT ON SCHEMA::Schema_A to ADGroup_C

However, one limitation that I see with this approach is that, whenever we add a new WinGroup to the database, we need to remember to explicitly DENY SELECT to that WinGroup on the schema. In other words, there is a maintenance overhead.

So, I wonder if there is a better way to implement this security requirement.

However, one limitation that I see with this approach is that, whenever we add a new WinGroup to the database, we need to remember to explicitly DENY SELECT to that WinGroup on the schema. In other words, there is a maintenance overhead.

So, I wonder if there is a better way to implement this security requirement.

One method is to create a role for all groups needing the denied permissions and add that role to db_datareader. Only add the group(s) needing select permissions on all schemas directly to db_datareader. For example:

--create custom data reader role
CREATE ROLE MyDataReader;
--add role to db_datareader to inherit select permissions
ALTER ROLE db_datareader
ADD MEMBER MyDataReader;
--deny permission on specific schema
DENY SELECT ON SCHEMA::Schema_A TO MyDataReader;
--add role memebers
ALTER ROLE MyDataReader
ADD MEMBER ADGroup_B;
ALTER ROLE MyDataReader
ADD MEMBER ADGroup_C;
--add only ADGroup_A directly to db_datareader
ALTER ROLE db_datareader
ADD MEMBER WinGroup_A;
GO

I just realised that Dan's solution (i.e. ALTER ROLE ... ADD MEMBER) will work only with SQL Server 2012. The database server in question is SQL Server 2008 R2. I should have stated this in the first place. My apologies Dan.

I will most probably go with David's solution.

To the question by Uri, we needed separate WinGroups, each catering to a separate functional teams within the business rather than one WinGroup for all.