none
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.

    Regards,

    Surya

    Sunday, July 07, 2013 1:07 PM

Answers

  • To add to what Dan said, the root problem is that you should not be using db_datareader.  If you don't want some users to read the whole database, don't put them in that role. 

    db_datareader is old.  It dates from before SQL supported schema and database level GRANTs.  Instead create a new role, grant it SELECT on the schemas you want it to access and add the groups to that.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by SQLSurya Monday, July 08, 2013 12:17 PM
    Sunday, July 07, 2013 2:56 PM

All replies

  • Why do you need another group? Is that possible to add a login to existing Winf group you have already denied.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 07, 2013 2:00 PM
  • 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

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 07, 2013 2:30 PM
  • To add to what Dan said, the root problem is that you should not be using db_datareader.  If you don't want some users to read the whole database, don't put them in that role. 

    db_datareader is old.  It dates from before SQL supported schema and database level GRANTs.  Instead create a new role, grant it SELECT on the schemas you want it to access and add the groups to that.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by SQLSurya Monday, July 08, 2013 12:17 PM
    Sunday, July 07, 2013 2:56 PM
  • Thank you all for the responses. 

    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.

    Regards,

    Surya

    Monday, July 08, 2013 12:28 PM
  • Although ALTER ROLE ... ADD MEMBER is new to SQL 2012, it simply adds first-class DDL for this.  You can perform the same action in earlier version with sp_addrolemember.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, July 08, 2013 1:25 PM