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?
dbo
schema, and nothing more. I'm an admin on the database instance so I can change user roles as necessary. If that's not enough information, please let me know what else you need.db_owner
database role?db_datareader
,db_datawriter
, anddb_ddladmin
roles, and I'm in those plusdb_owner
role.