Whitelist-based SQL Server schema setup

Question

My group has access to a single database on a SQL Server instance. We have a number of different people in our group, each working on separate projects. In trying to determine how to segregate projects within the database, I came across schemas, which seem to be a good way to define pseudo-databases within the single database instance, and segregate access as necessary at the schema level.

The problem I'm hitting is that a newly created schema seems to grant access to everybody, and I'm not sure how to enforce a whitelist approach. Do I have to manually deny access to all those not in a given group, or is there a mechanism to deny all access to a schema for all users by default and only grant access to those on a whitelist?

Answer 1

They can read/write in every schema because they have db_datareader and db_datawriter fixed roles assigned. These roles allow the user to read and write to any user table in the database ( http://msdn.microsoft.com/en-us/library/ms189121.aspx ).

(btw db_ddladmin also allows them to run ANY DDL commands in the database, which might cause you some problems if they start dropping/modifying each others objects)

If you revoke these role grants, you can then assign explicit schema permissions to a user, ie:

GRANT select, insert ON SCHEMA :: schema1 to user1;

More details at http://msdn.microsoft.com/en-us/library/ms187940.aspx

If you can't revoke these roles because they're in use elsewhere, then you'll need to explicitly deny permissions on the schema for the users you don't want to have access. (A deny at the schema level overrides the database level grant from the fixed role).