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?
Best Answer
They can read/write in every schema because they have
db_datareaderanddb_datawriterfixed 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_ddladminalso 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:
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).