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?

  • We need more info about the database principles that you are using. What roles/permissions do these users have? – Thomas Stringer Oct 23 '12 at 21:16
  • @ThomasStringer - From what I can tell, these users currently have full access (without grant option) to the default 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. – eykanal Oct 23 '12 at 22:44
  • Are they in the db_owner database role? – Thomas Stringer Oct 23 '12 at 23:11
  • @ThomasStringer - They're in the db_datareader, db_datawriter, and db_ddladmin roles, and I'm in those plus db_owner role. – eykanal Oct 23 '12 at 23:19

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.