Database Schema Access

Question

I have a database with multiple (10) schemas. I need a user to have access to two of them. If I grant access to the database and then GRANT SELECT to 2 schemas, is access to the other schemas DENY-ed by implication? Or must I grant permissions to those schemas and then DENY SELECT to the rest of them?

Answer 1

You should create roles and grant access to roles rather than granting users access directly to schema.

Once you assign permissions to the role, you can just add users to the role. This way you dont have to manage permissions for individual users. The users inherit permissions granted to role.

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the database
GRANT 
 SELECT, INSERT, UPDATE, DELETE, ALTER 
ON SCHEMA::dbo
 TO TableSelector 
GO
-- Add an existing user to the new role created 
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO
-- Revoke access rights on a schema from a role 
DENY ALTER -- you can customize here ...
ON SCHEMA::dbo
 TO TableSelector 

see my answer to : Setting user permissions for different SQL Server schemas

• David Browne - Microsoft: Note REVOKE and DENY are different. REVOKE removes a GRANT, DENY overrides it. So you can GRANT the whole schema and DENY one table, but not REVOKE it.
• Steve_Malcolm: As an Accidental DBA I sincerely appreciate this advice, but my base question remains unanswered. Does the "Principle of Least Privilege" apply here, or do I totally misunderstand the concept? Let me rephrase. Using the example above, if I grant SELECT to 'TableSelector' for one schema, is access to the other schema denied? Or do I need to explicitly DENY access to those schema?
• Kin Shah: If you grant select on one schema then the role wont have access to other schemas.
• Steve_Malcolm: Thanks Kin, I sincerely appreciate this. I will be implementing the roles as you suggest.